Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

REPLACE Multiple Spaces with One Expand / Collapse
Author
Message
Posted Monday, November 16, 2009 4:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #819285
Posted Monday, November 16, 2009 4:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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
Post #819287
Posted Monday, November 16, 2009 4:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #819298
Posted Monday, November 16, 2009 4:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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
Post #819301
Posted Monday, November 16, 2009 5:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #819306
Posted Monday, November 16, 2009 5:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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
Post #819307
Posted Monday, November 16, 2009 5:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #819312
Posted Monday, November 16, 2009 5:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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
Post #819313
Posted Monday, November 16, 2009 5:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:13 AM
Points: 225, Visits: 643
No worries, have to concentrate on other work now ... am getting Spaced out!
Cheers


C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #819315
Posted Monday, November 16, 2009 5:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:18 PM
Points: 242, Visits: 678
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
Post #819318
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse