CODEWITHSUNDEEP
sqlsql-server
Harish Mohanan
Harish Mohanan

Reputation: 184

Multiple Filter in the same column

I have a sql table with some values and a lot of filters 

ID | Name  | Filter1 | Filter2 | Filter3 | Filter4 ... and so on...

As now the filters have been set as int and I am running a query as follows to get the data required

select Name 
from tblABC 
where Filter1=1 and Filter2 = 7 and Filter3 = 33 ... and so on...'

My issue is that I want a filter column to hold multiple numbers. eg:- row no 3 will have numbers 8 and 13 in Filter1 cell, so that when I run a query for 8 or 13 I get the same result. ie I want both the below queries to return the same result. 

select... where  Filter1=8 
select... where  Filter1=13

How can this be done? I tried converting the Filter columns to nvarchar and entering data as .8.13. where '.' where was used as separators. After this, running a query 'select... where Filter1 LIKE '%.8.%' is working for me.. But there are like 12 Filter columns and when such a string search is run in large volumes, wouldn't it make the query slow. What would be a more efficient way of doing this?

I am using Microsoft SQL 2014

Upvotes: 2

Views: 24585

Answers (3)

ughai
ughai

Reputation: 9880

Another solution which you can try is to convert the columns to XML. Its better than converting the columns to VARCHAR. You can use .exist to get only the records matching your criteria. Something like this.

DECLARE @table1 TABLE
(
   [ID] int, [Name] varchar(9),Filter1 XML
)

INSERT INTO @table1
    ([ID], [Name],Filter1)
VALUES
    (1, 'Somename','<Filter>8</Filter>'),
    (2, 'Othername','<Filter>8</Filter><Filter>13</Filter>'),
    (3, 'Thirdname','<Filter>25</Filter>')


DECLARE @FilterValue INT = 8
SELECT Filter1.query('/Filter'),* 
FROM @table1
WHERE Filter1.exist('/Filter[. = sql:variable("@FilterValue")]') = 1

EDIT

You can even use the XML column to store all 12 of your filters. So this filter xml column which store all your filters and their multiple values. 

DECLARE @table1 TABLE
(
   [ID] int, [Name] varchar(9),Filter XML
)

INSERT INTO @table1
    ([ID], [Name],Filter)
VALUES
    (1, 'Somename','<Filter ID = "1"><FilterVal>8</FilterVal></Filter><Filter ID = "2"><FilterVal>3</FilterVal><FilterVal>12</FilterVal></Filter>'),
    (2, 'Othername','<Filter ID = "1"><FilterVal>8</FilterVal><FilterVal>13</FilterVal></Filter><Filter ID = "2"><FilterVal>8</FilterVal><FilterVal>13</FilterVal></Filter>'),
    (3, 'Thirdname','<Filter ID = "2"><FilterVal>12</FilterVal><FilterVal>25</FilterVal></Filter><Filter ID = "3"><FilterVal>33</FilterVal></Filter>')

DECLARE @Filter1Value INT = 8
DECLARE @Filter2Value INT = 12

SELECT * 
FROM @table1
WHERE Filter.exist('/Filter[@ID = 1]/FilterVal[. = sql:variable("@Filter1Value")]') = 1
AND   Filter.exist('/Filter[@ID = 2]/FilterVal[. = sql:variable("@Filter2Value")]') = 1

Upvotes: 0

CiucaS
CiucaS

Reputation: 2128

I'm going to post a solution I use. I use a split function ( there are a lot of SQL Server split functions all over the internet)

You can take as example 

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

and run your query like this

select Name 
from tblABC 
where Filter1 IN (
         SELECT * FROM SplitString(@DatatoFilter,',') and 
      Filter2 (IN (
         SELECT * FROM SplitString(@DatatoFilter,',') and 
..so on.

If you have hunderds of thousands of records it may not perform very well. But it should work.

My personal aproch would be a stored procedure and temp tables. Create a temp table with all the values you want to use as filter 

SELECT * 
INTO #Filter1
FROM SplitString(@DatatoFilter,',')

SELECT *
INTO #Filter2
FROM SplitString(@DatatoFilter,',')

then the final select

SELECT * FROM yourtable 
WHERE Filter1 IN (SELECT DISTINCT Part FROM #Filter1) and 
      Filter2 IN (SELECT DISTINCT Part FROM #Filter2)

I don't think it makes any big difference from the first query, but it is easier to read.

Upvotes: 1

Mackan
Mackan

Reputation: 6271

A more efficient way, hmm. Separating tblABC from the filters would be my suggested way to go, even if it's not the most efficient way it will make up for it in maintenance (and it sure is more efficient than using like with wildcards for it). 

tblABC            ID    Name
                  1     Somename
                  2     Othername

tblABCFilter      ID    AbcID     Filter
                  1     1         8
                  2     1         13
                  3     1         33
                  4     2         5

How you query this data depends on your required output of course. One way is to just use the following:

SELECT tblABC.Name FROM tblABC
INNER JOIN tblABCFilter ON tblABC.ID = tblABCFilter.AbcID
WHERE tblABCFilter.Filter = 33

This will return all Name with a Filter of 33.

If you want to query for several Filters:

SELECT tblABC.Name FROM tblABC
INNER JOIN tblABCFilter ON tblABC.ID = tblABCFilter.AbcID
WHERE tblABCFilter.Filter IN (33,7)

This will return all Name with Filter in either 33 or 7.

I have created a small example fiddle.

Upvotes: 3

Related Questions