SQL Server, replace one or more consecutive char

Question

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?

If it *just* spaces you want to replace? Or are there other characters? — Thom A, Nov 22 '21 at 15:26
@Luuk if you're going to recommend Regex you should be recommending a CLR function to use said Regex. Otherwise your linked duplicate is useless; SQL Server doesn't support Regex. — Thom A, Nov 22 '21 at 15:27
@Larnu, in this case, spaces, but if possible a general solution, should be better — elle0087, Nov 22 '21 at 15:29

score 2 · Accepted Answer · answered Nov 22 '21 at 15:57

No Need for a LOOP

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

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

score 0 · Answer 2 · answered Nov 22 '21 at 15:39

0

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.

answered Nov 22 '21 at 15:39

Golden Lion

2 Answers2