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?

CodePudding user response：

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

CodePudding user response：

use charindex https://www.w3schools.com/sql/func_sqlserver_charindex.asp in a looping structure and then use a variable to keep track of the index position.