rickyProgrammer rickyProgrammer - 3 months ago 10
SQL Question

Split String Value in SQL for Flexible Filtering

I have a function where in user can assign multiple categories (food, non food etc) to a certain Tenant. See sample Data Table

Table: tblSales

date tenant sales category
1/1/2015 tenant1 1000 Food,Non-Food,Kiosk
1/1/2015 tenant2 2000 Food
1/1/2015 tenant3 1000 Non-Food,Kiosk


The system should be able to load record when the user selected any of the categories listed in Category Column.

For example, User selected categories: Non-Food,Kiosk. Expected result should be:

date tenant sales category
1/1/2015 tenant1 1000 Food,Non-Food,Kiosk
1/1/2015 tenant3 1000 Non-Food,Kiosk


Since, Non-Food and Kiosk is seen in Tenants 1 and 3.

So, what I think, the process should be a string manipulation first on the value of Category column, splitting each word delimited by comma. I have code which does not work correctly

@Category nvarchar(500) = 'Non-Food,Kiosk' --User selected

SELECT date,tenant,sales,category
FROM tblSales
WHERE (category in (SELECT val FROM dbo.split (@Category, @delimeter)))


That does not seem to work because the one it is splitting is the User Selected Categories and not the value of the data itself. I tried this

@Category nvarchar(500) = 'Non-Food,Kiosk' --User selected

SELECT date,tenant,sales,category
FROM tblSales
WHERE ((SELECT val FROM dbo.split (category, @delimeter)) in (SELECT val FROM dbo.split (@Category, @delimeter)))


But it resulted to this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer

In addition to Tim's answer (he is absolutely right about CSV fields in databases!) please note that SQL Server 2016 introduced STRING_SPLIT function. For a single category it's as simple as:

SELECT
  date
 ,tenant
 ,sales
 ,category
FROM tblSales
WHERE @Category IN (SELECT value FROM STRING_SPLIT(category, ','))

For a comma delimited list of categories you have to use it twice together with EXISTS:

WHERE EXISTS
(
 SELECT *  
 FROM STRING_SPLIT(category, ',')  
 WHERE value IN (SELECT value FROM STRING_SPLIT(@Category, ','))
)

If you're using an older SQL Server version you may write your own STRING_SPLIT function, take a look to T-SQL split string. You can use that function with the same syntax as above (please note I wrote code here and it's untested so you may need some fixes).