Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Talking Unanswered: replace instance of multiple char(32)

    I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end

    What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word !

    Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space

    E.g. a field in a table contains the following :

    "my text field and loads of spaces then some more text with loads of spaces "

    I want to replace the char(32) with just one space so it looks like this :
    "my text filed and loads of spaces then some more text with loads of spaces"

    Thanks for your help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try using:
    Code:
    SELECT Replace('Target   string  with spaces', '  ', ' ')
    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I had to run the replace twice...

    declare @Targetstring varchar(50)
    set @Targetstring = '1 2 3 4 5 6'

    select @TargetString
    select Replace(@TargetString, ' ', ' ') --Not quite there yet....
    select Replace(Replace(@TargetString, ' ', ' '), ' ', ' ') --Lookin' good!
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Replace is kinda like that... It only makes one pass through the string on its own. It can be coerced into doing the right thing, if you have a big enough stick!

    -PatP

Posting Permissions

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