July 20, 2005 at 12:15 pm
I have a database with a table that includes a social security number as one of the fields. Half of the SSN's have hyphens and the other half don't. I'd like to run an SQL Query that will update the table by stripping out all the hyphens in the SSN's that have them. Can anyone provide me with some example code on how to do this? Thanks.
July 20, 2005 at 12:33 pm
try this
update SSNtable
set SSN = replace(SSN, '-', '')
hope it should work
July 21, 2005 at 11:39 am
It probably doesn't apply in this case, but because of the compatibility mode (65) set in our DB, that query would result in a number with spaces instead of hyphens.
update SSNtable
set SSN = substring(SSN,1,3) + substring(SSN,5,2) + substring(SSN,8,4)
where SSN like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
July 21, 2005 at 11:42 am
The first example worked out perfectly! I appreciate the help. Thanks.
July 21, 2005 at 12:20 pm
Hi Russell,
If u c the query again, I didn't give any spaces between the quotes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply