October 21, 2008 at 11:13 am
Found this function on Experts-Exchange and was wondering how to modify it to work for
social security numbers?
CREATE FUNCTION dbo.fn_FormatPhoneNumber (@Input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@input)>0
SET @input = SUBSTRING(@input, 1, PATINDEX('%[^0-9]%',@input)-1) +
SUBSTRING(@input, PATINDEX('%[^0-9]%',@input)+1, (DATALENGTH(@input)/2))
SET @input='('+LEFT(@input,3)+') ' +
SUBSTRING(@input, 4,3) + '-' +
SUBSTRING(@input, 7,4) +
CASE WHEN LEN(@input)>10 THEN
' ext. ' + RIGHT(@input, LEN(@input)-10)
ELSE ''
END
RETURN @input
END
October 21, 2008 at 11:20 am
Nevermind, got it working on my own.
Thanks for taking a look, though...
IF OBJECT_ID('dbo.fn_FormatSSN') IS NOT NULL
DROP FUNCTION dbo.fn_FormatSSN
GO
CREATE FUNCTION dbo.fn_FormatSSN (@Input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@input)>0
SET @input = SUBSTRING(@input, 1, PATINDEX('%[^0-9]%',@input)-1) +
SUBSTRING(@input, PATINDEX('%[^0-9]%',@input)+1, (DATALENGTH(@input)/2))
SET @input=LEFT(@input,3)+ '-' +
SUBSTRING(@input, 4,2) + '-' +
SUBSTRING(@input, 6,4)
RETURN @input
END
GO
October 21, 2008 at 11:21 am
This is what I came up with.
Declare @ssn varchar(20)
--Test1
Set @ssn = '1-111-1--111-1'
Select stuff(stuff(replace(@ssn,'-',''),4,0,'-'), 7,0,'-')
--Test2
Set @ssn = '111111111'
Select stuff(stuff(replace(@ssn,'-',''),4,0,'-'), 7,0,'-')
October 21, 2008 at 11:28 am
Definitely like Ken's way of doing it. No loops or calculations required.
😎
October 21, 2008 at 11:28 am
Hey, that's pretty cool. There's more than one way to skin a cat!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply