Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Red face Unanswered: Replace Double Space with Single Space in data

    Due to a mixture of sloppy programming and 1d1ot users, I have a growing problem with my database that I need to correct. I have a column called UserName that 'Ought' to contain Joe<space>Bloggs but alas I have many instances of Joe<space><space>Bloggs.

    I'd be most grateful if someone could help this first poster with a script/statement that would allow me to rip out double space and replace with single space please. And if there's time a bit of guidance that I can pass to the developers to prevent this occurring in the future would be good too.

    Cheers

    Simon

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE daTable 
       SET UserName = REPLACE(UserName,'  ',' ')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For the really bad ID20T users, you should consider writing a user-defined function that loops through a string and replaces double-spaces with single spaces until no double-spaces are left. That will handle the triple and quadruple space cases as well.
    You could then either put a constraint on the column to return an error if the developer's interface attempts to insert a double-space, or put a trigger on the column to automatically correct the data using the UDF you created.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Sep 2010
    Posts
    3

    Talking Sorted

    Quote Originally Posted by r937 View Post
    Code:
    UPDATE daTable 
       SET UserName = REPLACE(UserName,'  ',' ')
    Many thanks - worked a treat!

    For the OVER-cautious you could add:

    WHERE Surname ='bloggs' (or whatever reference you have beyond surname) to allow you to update one by one.

    r937 - there's no book at the end of your url - I might have bought it! :-)

  5. #5
    Join Date
    Sep 2010
    Posts
    3

    Thumbs up Nice

    Quote Originally Posted by blindman View Post
    For the really bad ID20T users, you should consider writing a user-defined function that loops through a string and replaces double-spaces with single spaces until no double-spaces are left. That will handle the triple and quadruple space cases as well.
    You could then either put a constraint on the column to return an error if the developer's interface attempts to insert a double-space, or put a trigger on the column to automatically correct the data using the UDF you created.
    Thanks - you went whoosh over MY head after 'writing a .... ' I shall pass it on though - cheers

Tags for this Thread

Posting Permissions

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