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?

Answer 1

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