SQL View S.S. Number

[mtorpy]

Is there a way to display a social security number stored in a single field and format it?

Stored : 522219635

Display: 522-21-9635

In Crystal Reports I can use:
Left ({UPR00100.SOCSCNUM},3)+'-'+Mid ({UPR00100.SOCSCNUM},4 ,2 )+'-'+Right ({UPR00100.SOCSCNUM},4 )

But I don't beliew SQL allows for this operator.

Any help would be great
- Matt

 

[fluteplr]

substring

 

[tlbroadbent]

In addition to using the Substring function in place of the Mid function, you can use the Stuff function to insert the dashes in the string.

Stuff(Stuff(UPR00100.SOCSCNUM,6,0,'-'),4,0,'-') Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Zathras]

Just a reminder. Some SSN's have one or more leading zeros.

You didn't specify that the column was defined as CHAR(9). If so, all is well. If not (e.g. defined as INT) then a little more is needed to restore the leading zero character(s).