Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


REPLACE Multiple Spaces with One


REPLACE Multiple Spaces with One

Author
Message
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
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
Why I wrote a sql query analyzer clone
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
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
Why I wrote a sql query analyzer clone
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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

Matt Whitfield
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
in your function, you know you are replacing with something that will eventually disappear. However, you could easily want to replace one space with two spaces in another scenario, and if REPLACE did that sort of looping internally, then it would never return, because it would never run out of things to replace.

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
Matt Whitfield (11/16/2009)
in your function, you know you are replacing with something that will eventually disappear. However, you could easily want to replace one space with two spaces in another scenario, and if REPLACE did that sort of looping internally, then it would never return, because it would never run out of things to replace.


?
Why would we want replace one space with 2 spaces when cleaning up text?

Your example code replaces one space with one space?
REPLACE(expression, ' ', ' ') 

?

I don't think fn_CleanUp has these issues.

C# Gnu
____________________________________________________


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

Matt Whitfield
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
It shows up like that because I didn't code format it in my post - i did type in two spaces. I am just trying to explain to you why REPLACE can't work in the manner you thought it was going to, and the reason is that if you wanted to replace an expression with another expression that contained the first expression, then it would never terminate.

I'm not saying your function has those issues - when I was saying 'I realised why it can't...' I was talking about the REPLACE built in function, not your one. Sorry, I wasn't clear on that.

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
No worries, have to concentrate on other work now ... am getting Spaced out!
Cheers

C# Gnu
____________________________________________________


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

dant12
dant12
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 711
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?


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


the article in question is for the sole purpose of avoiding this sort of approach.
looping is bad, necessary evil sometimes but not always.

--
Thiago Dantas
@DantHimself
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search