social security # format - 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

SQL Server 2008 Forums

Transact-SQL (2008)

social security # format

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-21 : 11:04:23

I need to format the numbers like 111-22-3333. How do I do this? The are also records that have 00000000 if it is empty. I need to not bring back anything

Dave
Helixpoint Web Development
http://www.helixpoint.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-21 : 11:59:24

I'm guessing that your data is stored as a 9 character string and you want to insert dashes for formatting. The best way to do that is let the front end handle the formatting or simply store it formatted. But, if you need to format it in SQL one way is to use a STUFF function:

DECLARE @Foo CHAR(9) = '111223333'

SELECT STUFF(STUFF(@Foo, 4, 0, '-'), 7, 0, '-')

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-21 : 12:19:50

What is the first rule of any tiered architecture? Display formatting is always done in the front end. You also confused rows with records. I will bet that you still think in file systems when data and application code were in one monolithic block of code and not in tiers.

Since you confused rows and records, do you have a CHECK() constraint on the SSN column to validate the data? Fields in records have no such thing; columns in rows do.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources