Adding dashes to ssn - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding dashes to ssn

Author

Topic

h2sut
Starting Member

40 Posts

Posted - 2008-05-30 : 16:38:52

Can anyone help with this. IF i have a ssn like 123459636 and i want to insert dashes into it how would i do that. Results would be 123-45-9636
The second problem is that some of the ssn dont have zero in them so some look like 1234567 i would have to pad with zero then trim from the right to 9 digits then insert the dashes not sure how to do that

results would be 001-23-4567

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-30 : 17:05:30

declare @St as varchar(11)
set @st='456789'

select case len(@st)
when 9 then (Substring(@st,1,3)+'-'+Substring(@st,4,2)+'-'+Substring(@st,6,4))
when 8 then ('0'+Substring(@st,1,2)+'-'+Substring(@st,3,2)+'-'+Substring(@st,5,4))
when 7 then ('00'+Substring(@st,1,1)+'-'+Substring(@st,2,2)+'-'+Substring(@st,4,4))
when 6 then ('000-'+Substring(@st,1,2)+'-'+Substring(@st,3,4))

end

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-30 : 17:53:13

Other way could be


DECLARE @ssn varchar(11)
SELECT @ssn = '1234567'
SELECT @ssn = REPLICATE(0,9-LEN(@ssn)) +  @ssn
SELECT @ssn = LEFT(@ssn,3) + '-' + SUBSTRING(@ssn,4,2) + '-' + RIGHT(@ssn,4)
SELECT @ssn

Thanks
Karunakaran

h2sut
Starting Member

40 Posts

Posted - 2008-05-30 : 17:57:50

I have a table of SSN so is there a way to do that in a update for the whole table. Below is just for one ssn

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-30 : 18:41:19

quote:
Originally posted by h2sut

I have a table of SSN so is there a way to do that in a update for the whole table. Below is just for one ssn


CREATE TABLE #ssn(ssn varchar(11))
INSERT INTO #ssn 
SELECT '123456789' UNION ALL
SELECT '12345678' UNION ALL
SELECT '1234567' UNION ALL
SELECT '123456' UNION ALL
SELECT '12345'

--- Approach #1 -- With 1 Statement

UPDATE #ssn SET ssn = LEFT(dt.new_ssn,3) + '-' + SUBSTRING(dt.new_ssn,4,2) + '-' + RIGHT(dt.new_ssn,4) 
FROM #ssn sn,
(
	SELECT ssn,(REPLICATE(0,9-LEN(ssn)) +  ssn) AS new_ssn FROM #ssn
) dt
WHERE dt.ssn = sn.ssn

--- Approach #2 -- With 2 Update Statement
--- I think this could faster than the previous one. 
--- With 5-6 Records, I could not test much.

UPDATE #ssn SET ssn = REPLICATE(0,9-LEN(ssn)) +  ssn
UPDATE #ssn SET ssn = LEFT(ssn,3) + '-' + SUBSTRING(ssn,4,2) + '-' + RIGHT(ssn,4)

DROP TABLE #ssn

Thanks
Karunakaran

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-30 : 18:42:12

[code]select
SSN,
NEW_SSN = stuff(stuff(right('000000000'+ssn,9),6,0,'-'),4,0,'-')
from
(
--TestData
select SSN = '999999999' union all
select SSN = '123456789' union all
select SSN = '1234567' union all
select SSN = '1' union all
select SSN = '0' union all
select SSN = ''
) a

Results:
SSN NEW_SSN
--------- -----------
999999999 999-99-9999
123456789 123-45-6789
1234567 001-23-4567
1 000-00-0001
0 000-00-0000
000-00-0000

(6 row(s) affected)[/code]

Just do this for the update:
[code]update MyTable set SSN = stuff(stuff(right('000000000'+ssn,9),6,0,'-'),4,0,'-')[/code]

CODO ERGO SUM

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-30 : 18:50:10

quote:
Originally posted by Michael Valentine Jones

Just do this for the update:
[code]update MyTable set SSN = stuff(stuff(right('000000000'+ssn,9),6,0,'-'),4,0,'-')

CODO ERGO SUM

Well, that was very simple.
So much to learn yet....

Thanks
Karunakaran

h2sut
Starting Member

40 Posts

Posted - 2008-05-30 : 18:53:26

Thanks everyone for your help. I really apprieciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources