Replacing variable length string with some word

问题:

Given the below string

Declare @string varchar(max)='abc ___________ deffns ___ cg _ hif _______hh ihs';

this is the Output required : (Every hypen irrespective of length should be replaced with lastname)

abc lastname deffns lastname cg lastname hif lastname hh ihs

The issue here is, there can be many Hypens of variable length(max length can be <20)...

I tried with many methods and settled with below approach..

select 
REPLACE(REPLACE(replace(stringcol,replicate('_',20),'LASTNAME'),
replicate('_',19),'LASTNAME'),
replicate('_',18),'LASTNAME')
 from table

Is there a way to do accomplish this efficiently..any advice would be most welcome

回答1:

First get rid of the multiple underscores, then do the replace.

Here is one method:

select replace(replace(replace(@string, '_', '><'
                              ), '<>', ''
                      ), '><', 'LASTNAME'
              )

回答2:

In C# you can use:

string str = Regex.Replace(s, @"(_)\1{5,}", Lastname);

It will match character _ if more than 5 _ occurs.