How to remove multiple spaces in a varchar?
-
Hi all, I am trying to figure out the best way to do this.
Basically I have a @wordlist varchar(1000) as my sproc's parameter. @wordlist can contain a single word, or multiple words separated with spaces(could be more than one since it's user input).
I am trying to figure out how to get this:
@wordlist = ('a bc d e f')
into
@wordlist = ('a,b,c,d,e,f')
So I need to figure out a way to get the multiple spaces down to one, so I can replace it with ,
Any suggestion?
-
REPLACE(@wordlist,' ',' ') will replace double spaces with single spaces. You may need to do this in a loop and check for the condition where no double spaces exist as user input may allow for more than 2 spaces. Once you get it down to a single space between each word, replace single spaces with commas.
-
Of course Life would be MUCH easier if this could just be validated at user input stage...
**ASCII stupid question, get a stupid ANSI !!!** -
The method of removing double spaces and then single space is brilliant. There was no need to check that in a loop, as after replacing two spaces (or multiple of it), we are left with just single space, if any.
Thanks for the enlightening tip.
-
Similar to what's already been suggested but accomplished in a single step...
REPLACE(REPLACE(@WordList,' ',' '),' ',',')
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply