Split String Value in SQL for Flexible Filtering

Question

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.

score 1 · Answer 1 · answered Sep 02 '16 at 10:04

1

In general, it is bad practice to store CSV data into a database column, because, as you are currently seeing, it renders many of the advantages a database has not usable.

However, I think you might be able to get away with just using LIKE. Assuming the user selected the categories Non-Food and Kiosk, you could try the following query:

SELECT date,
       tenant,
       sales,
       category
FROM tblSales
WHERE category LIKE 'Non-Food' OR
      category LIKE 'Kiosk'

answered Sep 02 '16 at 10:04

Tim Biegeleisen

502,043
27
286
360

Hi, I have no known options to do since that is what the requirement is asking, to be able to assign multiple categories in one tenant alone. So that when user selects any of the categories, it will be loaded correctly still. – rickyProgrammer Sep 02 '16 at 10:06
@rickyProgrammer The above query should do just this; if a user selects any category, then all records in which that category appears at least once will be returned. – Tim Biegeleisen Sep 02 '16 at 10:07
The answer might be not effective on a dynamic approach? since it is based on a parameter chosen by user dynamically. It is like this sir: 'Food,Non-Food,Kiosk' LIKE 'Non-Food,Kiosk' (will load) , 'Food' Like ' LIKE 'Non-Food,Kiosk' (will still load, when it should not) – rickyProgrammer Sep 02 '16 at 10:10
1

In practice, the parameters would probably be coming in through an API in your application layer. So a user would, e.g., select parameters from a drop down, and then your code would issue a SQL Server query. – Tim Biegeleisen Sep 02 '16 at 10:12

score 1 · Accepted Answer · edited May 23 '17 at 12:08

1

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).

Note about performance: from QP you can check how sub-queries will be executed, from a naive point of view I'd say CTE, temp-tables and sub-queries have roughly same performance (in this simple case) but if this code is performance critical you'd better perform some benchmark (with real data and a real-world access scenario).

edited May 23 '17 at 12:08

Community

1
1

answered Sep 02 '16 at 10:09

Adriano Repetti

65,416
20
137
208

What could be the alternative when I am not using the latest version yet? – rickyProgrammer Sep 02 '16 at 10:11
Write your own STRING_SPLIT function (and accept performance penilty) – Adriano Repetti Sep 02 '16 at 10:12
Yes I have that – rickyProgrammer Sep 02 '16 at 10:14
@rickyProgrammer You should move away from storing your data in CSV format. Even though there may tools to work with it, it won't scale very well, and forgot about using any kind of index. – Tim Biegeleisen Sep 02 '16 at 10:15
your second code @AdrianoRepetti might be the correct one, I will try it – rickyProgrammer Sep 02 '16 at 10:17
1

It already worked! thanks a lot, WHERE EXISTS works like a magic :) – rickyProgrammer Sep 02 '16 at 10:30

score 0 · Answer 3 · answered Sep 02 '16 at 10:29

Try with the below code .

Create a function to split delemited strings.

CREATE FUNCTION SplitWords
(    
  @Input NVARCHAR(MAX),
  @Character CHAR(1)
)
  RETURNS @Output TABLE (
  Item NVARCHAR(1000)
 )
 AS
 BEGIN
  DECLARE @StartIndex INT, @EndIndex INT

  SET @StartIndex = 1
  IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
  BEGIN
        SET @Input = @Input + @Character
  END

  WHILE CHARINDEX(@Character, @Input) > 0
  BEGIN
        SET @EndIndex = CHARINDEX(@Character, @Input)

        INSERT INTO @Output(Item)
        SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

        SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
  END

  RETURN
 END
 GO

create a input tabl inside your prcedure /script and keep the split data in that. here your input is @Category

DECLARE @input TABLE (item VARCHAR(50))
INSERT INTO @input
SELECT Item
FROM  [dbo].SplitWords (@Category, ',')

make a join using like operator with your actual table

SELECT DISTINCT  a.date,
     a.tenant,
     a.sales,
     a.category
 FROM tblSales s
   JOIN @input a
     ON category LIKE '%'+item+'%'

score 0 · Answer 4 · answered Sep 02 '16 at 10:59

You can try following SQL Select statement where I used my user defined SQL function for split string task

declare @Category nvarchar(500)  = 'Non-Food,Kiosk'
declare @cnt int = (select COUNT(*) from dbo.SPLIT(@Category,','))
;with cte as (
    select 
        t.*, COUNT(*) over (partition by tenant) cnt
    from dbo.SPLIT(@Category,',') u
    inner join (
        select
            tblSales.*, c.val
        from tblSales
        cross apply dbo.SPLIT(tblSales.category,',') c
    ) t on u.val = t.val
)
select distinct tenant from cte where cnt = @cnt

Split String Value in SQL for Flexible Filtering

4 Answers4

Related