Search code examples
sqlsql-serversplitchartrim

SQL Server, replace one or more consecutive char

With SQL Server, is it possible to replace one or more consecutive characters?

For example:

select replace(replace(replace('my string   to  split','  ',' '),'   ',' '),'    ',' ')

without using a loop?

Solution

  • No Need for a LOOP

    Here is a little technique Gordon Linoff demonstrated some time ago.

    1. Expand
    2. Elimnate
    3. Restore

    You can substitute any ODD combination of characters/strings pairs like §§ and ||

    Example

    Select replace(replace(replace('my string   to  split',' ','><'),'<>',''),'><',' ')

    or More Unique strings

    Select replace(replace(replace('my string   to  split',' ','§§||'),'||§§',''),'§§||',' ')

    Results

    my string to split