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

Question

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?

You should fix your table design, and remove the CSV data, instead moving each value to a separate row. Querying against CSV is hard, querying CSV inputs against CSV table data is _really_ hard, and you should try to avoid it. — Tim Biegeleisen, Jul 17 '19 at 15:45

Pawan Gupta · Accepted Answer · 2019-07-18T08:35:38.733

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

score 0 · Answer 2 · answered Jul 17 '19 at 15:48

0

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

answered Jul 17 '19 at 15:48

Tim Biegeleisen

502,043
27
286
360

Does it really check if all the values from the list are matched? Imagine if they were ten different values not ordered by ascending? – BohdanZPM Jul 17 '19 at 15:53
The exact query I wrote checks that both `154` and `155` are present in the same `ObjectID`. If you have a list of size greater than two, the query would change slightly. – Tim Biegeleisen Jul 17 '19 at 15:55
That's the point. the list of values to be checked comes as an input from a user, so we don't know how many of them there will be. – BohdanZPM Jul 17 '19 at 16:00
The approach I gave above is one canonical way to do this (CSV is not). If you are having a challenge in your application layer generating the query, then you might want to open a new question. – Tim Biegeleisen Jul 17 '19 at 16:02

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

2 Answers2