Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: 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:

    Code:
    UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')
    so, i tried this query and it does nothing.

    Code:
    UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')
    does anybody have any ideas? Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    SELECT Cast(SSN AS VARBINARY(20))
       FROM DefendantCase
       WHERE  -- you figure out what rows interest you
    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by bla4free
    ...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:
    Code:
    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:
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by Pat Phelan
    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:
    Code:
    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?

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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:

    Code:
     
    update DefendantCase
    SET SSN = 
    case
    when datalength(SSN) = 11
       THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SSN,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 is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by tomh53
    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:

    Code:
     
    update DefendantCase
    SET SSN = 
    case
    when datalength(SSN) = 11
       THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SSN,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:
    Code:
     
    update DefendantCase
    SET SSN = 
    case
    when datalength(SSN) = 11
       THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SSN,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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •