Format SSN

Looking for a UDF to format social security numbers - currently, the value is a simple 9 digit string  - need to format it like:  xxx-xx-xxxx
tbaseflugAsked:
Who is Participating?
 
adatheladConnect With a Mentor Commented:
Sorry, missed out BEGIN and END:

CREATE FUNCTION dbo.fxn_FormatSSN(@Value VARCHAR(9))
RETURNS VARCHAR(11)
AS
BEGIN
RETURN (SELECT LEFT(@Value, 3) + '-' + SUBSTRING(@Value,4,2) + '-' + RIGHT(@Value,4))
END
GO
0
 
adatheladCommented:
Hi,

I'm not sure how much checking you want/need to do, but I've assumed that you will always pass in a 9 character string to be formatted:

CREATE FUNCTION dbo.fxn_FormatSSN(@Value VARCHAR(9))
RETURNS VARCHAR(11)
AS
RETURN (SELECT LEFT(@Value, 3) + '-' + SUBSTRING(@Value,4,2) + '-' + RIGHT(@Value,4))
GO

Is there a possibility that the value passed in might not be 9 characters? If so, how would you like this handled?


0
 
tbaseflugAuthor Commented:
adathelad -

Thanks - I will give t a try - no chance of more than 9 chars
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
lluthienCommented:
hmm.

isn't this a kind of validation you would want to do on the client?
0
 
tbaseflugAuthor Commented:
adathelad -
When attempting to create the function provided - I am getting the below error:

Server: Msg 170, Level 15, State 31, Procedure fxn_FormatSSN, Line 4
Line 4: Incorrect syntax near 'RETURN'.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I believe STUFF() will be more efficient than concatenation.

Also, a function is a lot of overhead for doing that.  You might also consider a computed column on the table:

ALTER TABLE tableNameThatContainsSSN
ADD SSNEdited AS STUFF(STUFF(SSN, 4, 0, '-'), 7, 0, '-')


However, if you need or prefer a function:

CREATE FUNCTION dbo.FormatSSN (
    @SSN CHAR(9)
)
RETURNS VARCHAR(11)
AS
BEGIN
RETURN STUFF(STUFF(@SSN, 4, 0, '-'), 7, 0, '-')
END --FUNCTION


NOTE: This is just an alternative coding; please still give adathelad all/most of the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.