STRING_SPLIT with multi delimiter in SQL Server

Question

I want to split values based on multiple values like ',' & 'And', operators. But I used 'And' in split function it is not working, can someone help me to achieve this?

select distinct y.value from  history x
 cross apply STRING_SPLIT(x.query, ',') y 
where lower(rtrim(ltrim(y.value))) not in (
    select s.value from keys s cross apply STRING_SPLIT(y.value, 'And') z where lower((s.value)) like lower(rtrim(ltrim(z.value)))
);

Msg 214, Level 16, State 11, Line 23 Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.

Sample input Data:

student,teacher,tutor and principle and director

Output:

student
teacher
tutor
principle
director

Please include some sample data. There might another way to go about doing this. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 3, 2019 at 1:19
If "and" is the only word that is present in your input, then simply do a replace of And with a comma and then do a string split — Harry
– Harry, Commented May 3, 2019 at 1:27

Harry · Accepted Answer · 2019-05-03 02:39:39Z

4

If "and" is the only word that is present in your input, then simply do a replace of And with a comma and then do a string split

answered May 3, 2019 at 2:39

Harry

2,9691 gold badge23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

STRING_SPLIT with multi delimiter in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related