CODEWITHSUNDEEP
sqlsql-server
chrisSpaceman
chrisSpaceman

Reputation: 249

Replace part of SQL string from a substring up to the next comma

I have a comma separated string in a table that looks like this:

FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000, RemoveTitle:30, SixthTitle:1234

The 'Title' bit refers to a description of that value, the value itself is held after the ':', in other words the string is made of Title:Value pairs.

I want to remove part of the string - specifically the value with the label 'RemoveTitle'. i.e. I want this:

FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000, RemoveTitle:30, SixthTitle:1234

to become:

    FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000, SixthTitle:1234

Importantly, I can't rely on the title:value pairs being in the same order in the string, or on the length of the 'values' being the same.

I can try to use STUFF along the lines of:

DECLARE @StartSubString varchar(100)
SET @StartSubString = 'RemoveTitle:'

STUFF(TableName.columnName,
      CHARINDEX(@StartSubString, TableName.columnName ,1),
      ???, 
      '')

But In order to work out the length of the substring to Stuff, I need to know the position of the comma following the @StartSubString, which I'm not sure how to get. It may also be the last pair in the string, in which case there won't be a comma following, but I can deal with that issue.

Any help would be very helpfully received!

Upvotes: 0

Views: 480

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Even the brute force method is tricky, because you have to consider a lot of different possibilities -- the string being at the beginning, end, or not present at all. This should work under all circumstances:

with tablename as (
    select 'FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000, RemoveTitle:3012345, SixthTitle:1234 ' as col union all
    select 'FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000, RemoveTitle:3012345' as col union all
    select 'RemoveTitle:3012345' as col union all
    select 'FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000' as col
)
select t.col,
       coalesce(Stuff(t.col, v.loc, charindex(', ', t.col + ', ', v.loc) - v.loc+2, ''), t.col) as new_col
from tablename t cross apply
     (values (PatIndex('%' + @StartSubString +'%', t.col))
     ) v(loc);

Here is a db<>fiddle.

EDIT:

You can fix the trailing comma using trim():

   trim(', ' from coalesce(Stuff(t.col, v.loc, charindex(', ', t.col + ', ', v.loc) - v.loc+2, ''), t.col)) as new_col

Upvotes: 1

Stu
Stu

Reputation: 32609

As ugly as it is, the following is one way to remove your data element using standard string functions. this assumes each element is separated by ', ', you can adjust as necesary:

declare @remove varchar(50)='RemoveTitle';
with sampledata as (
    select 'FirstTitle:name1, SecondTitle:100000, ThirdTitle:600, FourthTitle:100000, RemoveTitle:30, SixthTitle:1234' v
)

select v Original, Stuff(v,s.p, IsNull(NullIf(CharIndex(', ',v,s.p),0),Len(v))-s.p+2,'') Replaced, s.p, CharIndex(', ',v,s.p)
from sampledata sd
cross apply (values(PatIndex(Concat('%',@remove,'%'),v)))s(p)

Upvotes: 1

Related Questions