CODEWITHSUNDEEP
sqlsql-server
luanstat
luanstat

Reputation: 65

How to split a string with multiple special characters or delimiters into separate fragments using SQL?

I have a dataset that has a string column as shown below:

StringCol
abcd_45dl/beta3,test
a56d/beta_46ab

I would like to split each fragment at every special character such as (/,-_^. etc). Currently I am using the following line but this only gives me the first fragment.

 SUBSTRING(terms, 0, PATINDEX('%[-!?@#$%^&*()_+=<>.:;/|\]%', terms))

The desired output would like like this:

StringCol StringPc
abcd_45dl/beta3,test abcd
abcd_45dl/beta3,test 45dl
abcd_45dl/beta3,test beta3
abcd_45dl/beta3,test test
a56d/beta_46ab a56d
a56d/beta_46ab beta
a56d/beta_46ab 46ab

Upvotes: 0

Views: 2112

Answers (1)

Stu
Stu

Reputation: 32619

If you're using SQL Server 2017+ it provides translate that can help here combined with string_split:

with sample as (
 select 'abcd_45dl/beta3,test' StringCol union all
 select 'a56d/beta_46ab'
)
select *
from sample
cross apply String_Split(Translate(StringCol,'_/',',,'),',')

Upvotes: 6

Related Questions