String_split function is not splitting delimiters in SQL Server 2019

Question

I am trying to split the values in LOB column, while keeping the first column intact using the script below

select * 
from [dbo].[ReqApp]
where lob in 
 (select value from string_split ('Staff;Wealth Management',';'))

The query returns this:

The desire outcome is this.

I am using SQL Server 2019. Please provide suggestions on how to modify my script to get the desired outcome.

While it's great that others have provided a working solution, you really NEED to understand why your query does not do what you expect if you want to write tsql effectively. If lob contains the value "x;y", it will never be in the set generated by splitting. E.g., "x;y" is not equal to "x" nor is it equal to "y". — SMor, Jun 10 '20 at 22:12

score 1 · Accepted Answer · answered Jun 10 '20 at 21:24

1

I think you want apply:

select ra.id, s.value as lob
from [dbo].[ReqApp] ra cross apply
     string_split(ra.lob, ';') s;

This produces the data you have. Your query though has a where, suggesting that you want filtering.

answered Jun 10 '20 at 21:24

Gordon Linoff

score 1 · Answer 2 · answered Jun 10 '20 at 21:24

1

Try using cross apply

SELECT id, value as lob 
FROM [dbo].[ReqApp]  
CROSS APPLY STRING_SPLIT(lob, ',');

answered Jun 10 '20 at 21:24

zealous

What can I do if I had an additional column [deparments] with delimiters and desired the results? How would you make that happen? [Department] = {HR; Payroll;Finance} – user716255 Jun 10 '20 at 21:32

2 Answers2