replace(convert(varchar(9),SSN),'-','') issue

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 2005 Forums

Transact-SQL (2005)

replace(convert(varchar(9),SSN),'-','') issue

Author

Topic

mozart_azul
Starting Member

4 Posts

Posted - 2012-03-09 : 12:09:26

Hello,
I have an output table with a field:
"ss_number varchar (9)"

My input field is "SSN varchar(11)" with dashes in it.

In my selection I'm coding as follows:

replace(convert(varchar(9),SSN),'-','')

The output is coming with no dashes but only 7 characters.

eg:
input: 123-45-6789
output: 1234567

Any advice?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-09 : 12:15:03

[code]replace(convert(varchar(11),SSN),'-','')[/code]Since SSN's data type is already varchar, you may not even need the conversion.[code]replace(SSN,'-','')[/code]
or, if you must have varchar(9),
[code]convert(varchar(9),replace(SSN,'-',''))[/code]

mozart_azul
Starting Member

4 Posts

Posted - 2012-03-09 : 12:45:37

Thanks,
Yes, your solution works perfect; but I'm facing a new issue in the same field. Some the entries have no dashes and my output from those entries is coming 7 characters.
eg:
input: 123 45 6789
output 123 45 67

Should I try to clean the data first or any other idea?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:52:28

convert(varchar(9),replace(replace(SSN,'-',''),' ',''))

i hope you dont have any other seperators!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mozart_azul
Starting Member

4 Posts

Posted - 2012-03-09 : 13:12:42

Thanks, sunitabeck and visakh6 for your replays.
The solutions from both of you works perfect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 21:41:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources