Suppose that you have a column that stored multiple values in a single row using some delimiter as ‘,’. Now you are tasked with splitting these values into a separate row for each value and you need to do this on multiple rows in the table.
1: select distinct r.items
2: from Order o
3: cross apply dbo.split(o.Products,',') r
Here the Order Table has a Products column that has multiple Products stored in a single column separated with commas like ‘Tea,Coffee’ associated with a single Order. What the above query does is it gives a distinct list of names of all Products stored in all rows in the Order table. The Split() is a UDF which splits the string into one row for each value
Similarly if you need to check if these multiple values stored in the Products column contain a particular string from a list of values, i,e,
1: select o.* from Company c
2: join Order o on c.ItemID = o.companyItemID
3: where (select * from dbo.split(o.Products) in ('TBD','Tea', 'Coffee'))
Since the above would not compile hence the right solution to do something like this would be:
1: select o.* from Company c
2: join Order o on c.ItemID = o.companyItemID
3: cross apply dbo.Split(Products,',') p
4: where p.Items in ('TBD','Tea','Coffee')
what we are doing here is a inner join on the Split() table valued UDF which returns a table containing one row for each value split and then comparing each Item to see if it matches one of the product names i.e. Tea or Coffee.
