REPLACE Multiple Spaces with One

  • Comments posted to this topic are about the item REPLACE Multiple Spaces with One

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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/

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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:

  • Nice one jeff.

    "Keep Trying"

  • 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?

  • 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

  • Richard Briggs (11/16/2009)


    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?

    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

  • 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,' ',' '),' ',' ')

  • Actually typo in posted code, the second REPLACE is also restricted to 6 spaces, I wonder why?

    Cheers

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

  • 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

  • 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

  • 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

  • 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?

Viewing 15 posts - 1 through 15 (of 426 total)

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