CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
TheGameiswar
TheGameiswar

Reputation: 28920

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

Upvotes: 0

Views: 550

Answers (2)

JVL
JVL

Reputation: 54

In C# you can use:

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

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270341

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

Here is one method:

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

Upvotes: 14

Related Questions