Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

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
0
tbaseflug
Asked:
tbaseflug
2 Solutions
 
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
 
lluthienCommented:
hmm.

isn't this a kind of validation you would want to do on the client?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
adatheladCommented:
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
 
Scott PletcherSenior 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now