REPLACE Multiple Spaces with One

Question

Post reply

REPLACE Multiple Spaces with One

Jeff Moden

SSC Guru

Points: 1004339
More actions
November 14, 2009 at 12:21 pm

#217625

Comments posted to this topic are about the item REPLACE Multiple Spaces with One
--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 15 posts - 1 through 15 (of 425 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Very nice solution and a great explanation for the solution.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Thanks Adi... it's an old solution and it's not mine. I've just never seen anyone explain it before and thought an explanation was long overdue especially since it's been coming up a lot on the forums, again. A lot of the "answer" posts have included a substantial amount of RBAR and, of course, that drove me to write the article.

--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)

John McC Hall of Fame Points: 3410 More actions · Answer 3

Another (very good) example of something that initially appears to be a horrendous problem having an elegant and relatively simple solution.

Must add to briefcase 🙂

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

ChiragNS

One Orange Chip

Points: 26137

November 16, 2009 at 2:19 am

#1079560

Nice one jeff.

"Keep Trying"

C# Gnu SSCertifiable Points: 5973 More actions · Answer 5

Hi

Does this simple replace not work then?

REPLACE(@SomeText, ' ',' ')

which is actually :

REPLACE(@SomeText, '[space][space]','[space]')

i.e. replace two spaces with one..

This seams to work fine for me, but I could well be missing something?

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 6

Jeff

Nice article - I just hope that when I *finally* get round to writing mine then it's anywhere near one tenth as good as yours generally are...

M

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

dant12 SSCrazy Points: 2712 More actions · Answer 7

Richard Briggs (11/16/2009)
Hi
Does this simple replace not work then?
REPLACE(@SomeText, ' ',' ')
which is actually :
REPLACE(@SomeText, '[space][space]','[space]')
i.e. replace two spaces with one..
This seams to work fine for me, but I could well be missing something?

sure, it works. if you only have 2 spaces it will work. the problem on the article however is for when u have a random amount of spaces.

with your example you would have to manually add a REPLACE for each extra space

--
Thiago Dantas
@DantHimself

C# Gnu SSCertifiable Points: 5973 More actions · Answer 8

I think you need to try this out ... the above comment (sorry dant12) is not correct:

It looks like the following will replace up to six spaces

REPLACE(@SomeText, ' ',' ')

And the following will sort the rest out :

REPLACE(REPLACE(@SomeText, ' ',' '),' ',' ')

try this one :

DECLARE @sample VARCHAR(1000)

SET @sample= 'ALKSDKLKJ LSJD ASD LSD S D DJD D D D D D D'

SELECT REPLACE(REPLACE(@sample,' ',' '),' ',' ')

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 9

Actually typo in posted code, the second REPLACE is also restricted to 6 spaces, I wonder why?

Cheers

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 10

So we have to loop to get around 6 character limit of REPLACE:

I cant understand why REPLACE has that 6 char limit though?

ALTER FUNCTION dbo.fn_CleanUp(@FooString VARCHAR(max))

RETURNS VARCHAR(max)

BEGIN

WHILE CHARINDEX(' ',@FooString) > 0

SELECT @FooString = REPLACE(@FooString,' ',' ')

RETURN @FooString

END

GO

-- Example

DECLARE @sample VARCHAR(1000)

SET @sample= 'ALKSDKLKJ LSJD ASD LSD S D DJD D D D D D D'

SELECT dbo.fn_CleanUp(@sample)

I think the 6 char limit is a MS bug!:-)

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 11

Richard Briggs (11/16/2009)
So we have to loop to get around 6 character limit of REPLACE:
I cant understand why REPLACE has that 6 char limit though?
I think the 6 char limit is a MS bug!:-)

I think you have a fundamental misunderstanding of how REPLACE works. When it replaces characters, it replaces them, and moves on to the next characters of the string. It does not then check if what you have replaced the source with matches the expression again... So

REPLACE(REPLACE(@sample,' ',' '),' ',' ')

edit -> is similar to ~~is directly equivalent to~~

REPLACE(@sample,' ',' ')

and what it seems you're expecting it to do is something similar to:

WHILE CHARINDEX(' ', @sample) > 0

BEGIN

SET @sample = REPLACE(@sample,' ',' ')

END

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

C# Gnu SSCertifiable Points: 5973 More actions · Answer 12

Yes you are right, I confused my self with the original typo which was

SELECT REPLACE(@sample,' ','')

(replacing with nothing)

So sorry about that.

At least the function fn_Cleanup works fine 🙂

Cheers

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 13

Indeed

I also realised why it can't work in a 'loop till it's finished' kind of way - because if you did REPLACE(expression, ' ', ' ') then it would never terminate...

M

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

C# Gnu SSCertifiable Points: 5973 More actions · Answer 14

Matt Whitfield (11/16/2009)
Indeed
I also realised why it can't work in a 'loop till it's finished' kind of way - because if you did REPLACE(expression, ' ', ' ') then it would never terminate...
M

?

but loop until no more double spaces is ok as in function fn_CleanUp?

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015