REPLACE Multiple Spaces with One

  • Jeff Moden

    SSC Guru

    Points: 993924

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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/

  • Jeff Moden

    SSC Guru

    Points: 993924

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • John McC

    Hall of Fame

    Points: 3410

    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:

  • ChiragNS

    One Orange Chip

    Points: 26137

    Nice one jeff.

    "Keep Trying"

  • C# Gnu

    SSCertifiable

    Points: 5973

    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?

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    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

  • dant12

    SSCrazy

    Points: 2652

    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

  • C# Gnu

    SSCertifiable

    Points: 5973

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

  • C# Gnu

    SSCertifiable

    Points: 5973

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

    Cheers

  • C# Gnu

    SSCertifiable

    Points: 5973

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

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    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

  • C# Gnu

    SSCertifiable

    Points: 5973

    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

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    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

  • C# Gnu

    SSCertifiable

    Points: 5973

    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