SQL Replace string between different charaters in comma delimited list

Question

I am looking to remove the text between two different special characters in a string. The string is a comma-delimited list that contains a value that is immediately followed by special characters with a substring between. The characters and substring can appear once, multiple times, or not at all in the comma delimited list. Also, the substring between the two characters is never the same. I'd like to remove anything between the greater/less than symbols, as well as those two characters. Please note I do not have permission to create temp functions in this database.

Current result:

PersonID     Loc     Code
12345    LOC1    M25.532<1010771>, S52.572A<1010772>, S66.892A<1010773>
12346    LOC2    M20.530<1010652>, S52.573A<1010752>
12347    LOC3    M29.52<1045201>

Desired result:

PersonID     Location Code
12345    LOC1     M25.532, S52.572A, S66.892A
12346    LOC2     M20.530, S52.573A
12347    LOC3     M29.52

This sound like a job for a regex. Please specify the database you are using since regex support varies among products. — lit, Apr 25 '17 at 01:59

SqlZim · Answer 1 · 2017-04-25T12:49:30.903

For SQL Server:

In SQL Server 2016+ you could use string_split() and in SQL Server 2017+ string_agg() (but string_split() does not return an ordinal).

Prior to SQL Server 2016... using a CSV Splitter table valued function by Jeff Moden we can split on <, and use stuff() to remove the portion up to > inside the subquery that is concatenating the strings back together using the select ... for xml path ('') method of string concatenation:

select 
    t.PersonID
  , t.Loc
  , Code = (
      select stuff(s.Item,1,charindex('>',s.Item),'')
      from [dbo].[delimitedsplit8K](t.Code,'<') s
      where s.item<>''
      order by s.ItemNumber
      for xml path (''), type).value('.','varchar(8000)')
from t

rextester demo: http://rextester.com/JONQ18668

returns:

+----------+------+-----------------------------+
| PersonID | Loc  |            Code             |
+----------+------+-----------------------------+
|    12345 | LOC1 | M25.532, S52.572A, S66.892A |
|    12346 | LOC2 | M20.530, S52.573A           |
|    12347 | LOC3 | M29.52                      |
+----------+------+-----------------------------+

splitting strings reference:

SQL Replace string between different charaters in comma delimited list

1 Answers1