REPLACE Multiple Spaces with One

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    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

  • C# Gnu

    SSCertifiable

    Points: 5973

    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.

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    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

  • C# Gnu

    SSCertifiable

    Points: 5973

    No worries, have to concentrate on other work now ... am getting Spaced out!

    Cheers

  • dant12

    SSCrazy

    Points: 2602

    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

  • ealter

    SSC Journeyman

    Points: 99

    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.

  • dsdeming

    Ten Centuries

    Points: 1236

    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

  • Steve McRoberts-357330

    Old Hand

    Points: 349

    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 varchar(max) output

    as

    SET NOCOUNT ON

    set @test = replace(@test,char(9),' ')

    set @test = replace(@test,char(10),' ')

    set @test = replace(@test,char(13),' ')

    set @test = replace(@test,'''', '''''')

    CREATE TABLE #words(seq int identity, singlePhrase varchar(8000))

    -- Split by words and load text into the table

    SELECT @test = 'INSERT INTO #words(singlePhrase) SELECT A=''' +

    REPLACE(@test, ' ', ''' UNION ALL SELECT ''') + ''''

    EXECUTE(@test);

    set @test = '';

    select @test = @test + singlePhrase + ' ' FROM #words where ltrim(singlePhrase) > '' order by seq;

    drop table #words

    I think I'll switch over to your method.

    Thanks!

  • C# Gnu

    SSCertifiable

    Points: 5973

    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

  • Tom Garth

    SSCertifiable

    Points: 6173

    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!

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Silverfox

    SSC-Insane

    Points: 23090

    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]

  • mjswart

    SSC Enthusiast

    Points: 168

    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.

  • Fatherjack

    SSCommitted

    Points: 1573

    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

  • jcrawf02

    SSC-Insane

    Points: 24198

    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."

  • dhananjay.n.kulkarni

    SSC Journeyman

    Points: 80

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

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