CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Asieh hojatoleslami
Asieh hojatoleslami

Reputation: 3360

compare some lists in where condition sql

I have some question in Sqlserver2012. I have a table that contains a filed that save who System Used from this information and separated by ',', I want to set into parameter the name of Systems and query the related rows:

declare @System nvarchar(50)
set @System ='BPM,SEM'

    SELECT * 
       FROM dbo.tblMeasureCatalog t1
where  (  ( select Upper(value) from dbo.split(t1.System,','))
                         = any( select Upper(value) from dbo.split(@System,',')))

dbo.split is a function to return systems in separated rows

Upvotes: 1

Views: 178

Answers (3)

GarethD
GarethD

Reputation: 69769

Forgetting for a second that storing delimited lists in a relational database is abhorrent, you can do it using a combination of INTERSECT and EXISTS, for example:

DECLARE @System NVARCHAR(50) = 'BPM,SEM';
DECLARE @tblMeasureCatalog TABLE (System VARCHAR(MAX));
INSERT @tblMeasureCatalog VALUES ('BPM,XXX'), ('BPM,SEM'), ('XXX,SEM'), ('XXX,YYY');

SELECT  mc.System
FROM    @tblMeasureCatalog AS mc
WHERE   EXISTS
        (   SELECT  Value
            FROM    dbo.Split(mc.System, ',')
            INTERSECT
            SELECT  Value
            FROM    dbo.Split(@System, ',')
        );

Returns

System
---------
BPM,XXX
BPM,SEM
XXX,SEM

EDIT

Based on your question stating "Any" I assumed that you wanted rows where the terms matched any of those provided, based on your comment I now assume you want records where the terms match all. This is a fairly similar approach but you need to use NOT EXISTS and EXCEPT instead:

Now all is still quite ambiguous, for example if you search for "BMP,SEM" should it return a record that is "BPM,SEM,YYY", it does contain all of the searched terms, but it does contain additional terms too. So the approach you need depends on your requirements:

DECLARE @System NVARCHAR(50) = 'BPM,SEM,XXX';
DECLARE @tblMeasureCatalog TABLE (System VARCHAR(MAX));
INSERT @tblMeasureCatalog 
VALUES 
    ('BPM,XXX'), ('BPM,SEM'), ('XXX,SEM'), ('XXX,YYY'), 
    ('SEM,BPM'), ('SEM,BPM,XXX'), ('SEM,BPM,XXX,YYY');



-- METHOD 1 - CONTAINS ALL SEARCHED TERMS BUT CAN CONTAIN ADDITIONAL TERMS
SELECT  mc.System
FROM    @tblMeasureCatalog AS mc
WHERE   NOT EXISTS
        (  
            SELECT  Value
            FROM    dbo.Split(@System, ',')
            EXCEPT
            SELECT  Value
            FROM    dbo.Split(mc.System, ',')
        );


-- METHOD 2 - ONLY CONTAINS ITEMS WITHIN THE SEARCHED TERMS, BUT NOT 
-- NECESSARILY ALL OF THEM  
SELECT  mc.System
FROM    @tblMeasureCatalog AS mc
WHERE   NOT EXISTS
        (   SELECT  Value
            FROM    dbo.Split(mc.System, ',')
            EXCEPT
            SELECT  Value
            FROM    dbo.Split(@System, ',')
        );

-- METHOD 3 - CONTAINS ALL ITEMS IN THE SEARCHED TERMS, AND NO ADDITIONAL ITEMS
SELECT  mc.System
FROM    @tblMeasureCatalog AS mc
WHERE   NOT EXISTS
        (   SELECT  Value
            FROM    dbo.Split(@System, ',')
            EXCEPT
            SELECT  Value
            FROM    dbo.Split(mc.System, ',')           
        )
AND     LEN(mc.System) = LEN(@System);

Upvotes: 2

Hardik
Hardik

Reputation: 228

you can try this : 

declare @System nvarchar(50)
set @System ='BPM,SEM'

SELECT * from dbo.tblMeasureCatalog t1 inner join  dbo.Split    (@System ,',') B on t1.it=B.items

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You have a problem with your data structure because you are storing lists of things in a comma-delimited list. SQL has a great data structure for storing lists. It goes by the name "table". You should have a junction table with one row per "measure catalog" and "system".

Sometimes, you are stuck with other people's really bad design decisions. One solution is to use split(). Here is one method:

select mc.* 
from dbo.tblMeasureCatalog mc
where exists (select 1
              from dbo.split(t1.System, ',') t1s join
                   dbo.split(@System, ',') ss
                   on upper(t1s.value) = upper(ss.value)
             );

Upvotes: 1

Related Questions