removing spaces from a text

Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:

UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')

so, i tried this query and it does nothing.

UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')

does anybody have any ideas? Thanks!Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP|||...So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:
UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')The sample code you gave will do exactly what you wanted it to do, and would not have inserted spaces where the dashes were:select REPLACE('123-45-6789', '-','')So something else is going on. Either you have dirty characters in your data, or you ran a different statement than the one you posted.|||Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP
okay, i ran your code and i'm getting this in the results: 0x20
Is that a space?|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.
thanks for your post! your solution worked perfectly except i had to use this:

update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,8,4)
ELSE SSN
end

i had to insert an 8 b/c the last 4 digits of a ssn start at 8. but thanks for the help though! you solved my problem perfectly!