tbaseflug
asked on
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
ASKER
adathelad -
Thanks - I will give t a try - no chance of more than 9 chars
Thanks - I will give t a try - no chance of more than 9 chars
hmm.
isn't this a kind of validation you would want to do on the client?
isn't this a kind of validation you would want to do on the client?
ASKER
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'.
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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?