November 14, 2009 at 12:21 pm
Comments posted to this topic are about the item REPLACE Multiple Spaces with One
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2009 at 3:07 am
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/
November 15, 2009 at 10:26 am
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
Change is inevitable... Change for the better is not.
November 16, 2009 at 2:03 am
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:
November 16, 2009 at 2:19 am
Nice one jeff.
"Keep Trying"
November 16, 2009 at 2:43 am
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
____________________________________________________
November 16, 2009 at 2:50 am
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
November 16, 2009 at 3:13 am
Richard Briggs (11/16/2009)
HiDoes 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
November 16, 2009 at 3:18 am
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
____________________________________________________
November 16, 2009 at 3:27 am
Actually typo in posted code, the second REPLACE is also restricted to 6 spaces, I wonder why?
Cheers
C# Gnu
____________________________________________________
November 16, 2009 at 4:00 am
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
____________________________________________________
November 16, 2009 at 4:07 am
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
November 16, 2009 at 4:56 am
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
____________________________________________________
November 16, 2009 at 4:58 am
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
November 16, 2009 at 5:09 am
Matt Whitfield (11/16/2009)
IndeedI 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
____________________________________________________
Viewing 15 posts - 1 through 15 (of 425 total)
You must be logged in to reply to this topic. Login to reply