REPLACE Multiple Spaces with One
-
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[/url]
Why I wrote a sql query analyzer clone -
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 -
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[/url]
Why I wrote a sql query analyzer clone -
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 -
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 -
Excellent, clever "trick"! I hope you don't mind if I use your code! Question in regard to finding the best replacement character: wouldn't it be a simple matter to use a short 2-character replacement string (instead of the "X" in the example)? This would minimize even more the likelihood of clashing with real data. For example, using "|>" (pipe-greater than) would have a very low probability of clashing with real data, and the modification to the code would be trivial.
-
Nice work, Jeff. Particularly the discussion of the potential gotchas in the choice of the "unlikely" character. It never would have occured to me that using something like the Thorn character could have such unforseen consequences.
Dennis
-
Jeff, you've come up with a nice, simple method for doing this. It made me go back and look at how I had coded it in my application. My code seems to avoid RBAR, and also deals with other "space" characters (tabs and cr/lf) [something that could be easily added to your method, of course.] But my code is more convulted and probably less efficient due to the creation of a temp table to split the words into:
CREATE procedure [dbo].[removeExtraWhiteSpace]@test-2 varchar(max) output
as
SET NOCOUNT ON
set @test-2 = replace(@test,char(9),' ')
set @test-2 = replace(@test,char(10),' ')
set @test-2 = replace(@test,char(13),' ')
set @test-2 = replace(@test,'''', '''''')
CREATE TABLE #words(seq int identity, singlePhrase varchar(8000))
-- Split by words and load text into the table
SELECT @test-2 = 'INSERT INTO #words(singlePhrase) SELECT A=''' +
REPLACE(@test, ' ', ''' UNION ALL SELECT ''') + ''''
EXECUTE(@test);
set @test-2 = '';
select @test-2 = @test-2 + singlePhrase + ' ' FROM #words where ltrim(singlePhrase) > '' order by seq;
drop table #words
I think I'll switch over to your method.
Thanks!
-
the article in question is for the sole purpose of avoiding this sort of approach.
looping is bad, necessary evil sometimes but not always.
Yes sorry Thiago
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
How very elegant, and cool. It's so well explained that I know I'll remember it the next time I have to do some cleanup.
Thanks Jeff!
-
Good Article Jeff, appreciate you sharing it with the rest of us 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url] -
If we change domains slightly: Consider a text-editing problem rather than a programming problem:
When working in Management Studio if you want to replace one (and only one) string of spaces with a single space instance. Ctrl+K+\ works nicely.
-
Guys,
Rather than get over concerned about collation for choosing the character to replace the 2nd space you can use any string, so you could use
SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(OriginalString,' ',' ||*9*9||'),'||*9*9|| ',''),'||*9*9||','')
))
FROM @demo
its just a case of picking a delimiter that isnt 'ever' going to be part of the string that you are splitting...
Jeff: thanks for the post, good to have the example online for reference
Jonathan
-
My first thought was "why the intermediate step, why not just replace double occurrences of space with nothing?", but after testing it, it obviously doesn't work because you don't know if those are the only spaces. (my second thought was "Well, because Jeff said so, and he tests *everything*, but what the heck" ;-))
Great article, because it prompted me to go play with the examples, and now I really understand why it works.
Thanks again, Jeff!:-D
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
Hi , Article is nice. I have little very quick one liner solution for this :
declare @str_having_multiplespaces varchar(200)
set @str_having_multiplespaces = 'hi onespace 2spaces 3spaces 25spaces'
select replace(@str_having_multiplespaces , space(2) , space(0))
--- REsult === >>> hi onespace2spaces 3spaces 25spaces
Try this and let us know if this faster
Viewing 15 posts - 16 through 30 (of 425 total)
You must be logged in to reply to this topic. Login to reply