MS SQL Server nested data

It always was and stilll is a nice way to be able to filter multiple codes by using something splitting such a string into multiple records to inner join with them.

In the past you had to do a user-defined function for splitting CSV values based on a tally table for that, see http://www.sqlservercentral.com/articles/Tally+Table/72993/

Since a few years, there is STRING_SPLIT (Starting in SQL Server 2016 and minimum COMPATIBILITY_LEVEL 130)

DECLARE @data as Table (ID int, [codes] Varchar(128))

INSERT INTO @data values (1,',1000,2002,3002,4000,4000,'),(2,'123,,456')

SELECT ID,value as Code from @data
cross apply string_split([codes],',') where value<>''

Select allOpen in new window

You may want to trim off the leftmost and rightmost comma in your case, I simply did that with the WHERE value<>'', but it might be valid some items in the list are empty, especially in cases this is not about numbers.

Notice the type of value still is varchar (read books online about String_Split), to get numbers simply use CAST or CONVERT.

PS: You might even find such a split function in your database, specialized on the case of CSV delimited with extra commas. I can see how that may simplify code finding all items between two commas, as begin and end of a field are no special case anymore.

Bye, Olaf.

@Olaf
Yes, string_split was introduced at SQL Server 2016. Sorry. I had just recently used string_agg and string_split in an answer it was string_agg that came in 2017. Cheers.

Thank You for the help