CODEWITHSUNDEEP
sqlsql-servercsvsql-match-all
BohdanZPM
BohdanZPM

Reputation: 755

SQL Server: How to check if values in a column match ALL the values from a subquery?

I have a table with a column containing values in a comma-separated list, like so:

| ObjectID (int) | Column1 (nvarchar(max))|
|              1 | 152, 154, 157, 158     |
|              2 | 101, 154, 155          |
|              3 | 97, 98, 99             |

I need to select all the ObjectIDs that have Column1 with ALL the integer values found in a particular string: '155, 154'. That way, only row with ObjectID = 2 should be returned, as it contains both 155 and 154 integers in its Column1 string.

I was trying something like this:

DECLARE @SearchString nvarchar(max) = '155,154';
SELECT *
FROM ObjectsTable
WHERE EXISTS
    (SELECT Data FROM dbo.SplitString(Column1, ',')
    WHERE Data = ALL (SELECT Data FROM dbo.SplitString(@SearchString, ',')))

But unfortunately, it does not return any records for me. Any idea how I can handle this, if it's even possible?

Upvotes: 0

Views: 99

Answers (2)

Pawan Gupta
Pawan Gupta

Reputation: 187

You can find the object id by using this query: 

declare @FindText varchar(50)
set @FindText = '155,154'

    SELECT Final.ObjectID
FROM (
    SELECT Tmp.ObjectID, COUNT(DISTINCT Found.Item) FoundCount
    FROM (
        SELECT A.ObjectID, A.Column1, B.Item 
        from ObjectsTable A 
        outer apply dbo.SplitString(Column1,',') B
    ) Tmp
    OUTER APPLY dbo.SplitString(@FindText,',') Found 
    WHERE LTRIM(Found.Item) = LTRIM(Tmp.Item)
    GROUP BY Tmp.ObjectID
) Final
INNER JOIN (SELECT COUNT(*) FindCount FROM dbo.SplitString(@FindText,',')) AS Fnd ON Fnd.FindCount = Final.FoundCount

This is dynamically find the number of objectIDs as the value change in @FindText So I think this will be very much helpfull to you.

Demo

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

The best answer here is to fix your data model, and normalize your table to this:

ObjectID | Column1
1        | 152
1        | 154
1        | 157
1        | 158
2        | 101
2        | 154
2        | 155
3        | 3
3        | 97
3        | 98
3        | 99

Now your query can be simplified to this:

SELECT ObjectID
FROM yourUpdateTable
WHERE Column1 IN (155, 154)
GROUP BY ObjectID
HAVING MIN(Column1) <> MAX(Column1);

Upvotes: 0

Related Questions