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

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:

CodePudding user response：

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,'_/',',,'),',')