Search for value of split field

Question

I have a field with multiple foreign keys which I need to search for a given value.

Say admin_ids :: 24,56,78

How do I search that field and get the following results: 24 = true; 6 = false; 7 = false

I cannot change the schema.

Any ideas?

Answer 1

What I've done in the past is create a table valued function to split the string into the distinct values, provided they're delimited by the same...delimiter.

There are examples out there on the web, for instance: http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

Here is an example, converted into a script:

DECLARE   
    @Data NVARCHAR(MAX) = '24,56,78',
    @Delimeter NVARCHAR(MAX) = ','

DECLARE @Values TABLE (ID INT IDENTITY(1,1), Data NVARCHAR(MAX)) 
DECLARE @Users  TABLE (ID INT)
INSERT INTO @Users (ID)
VALUES (1),(6),(7),(24),(30),(56)

DECLARE @Iterator INT
SET @Iterator = 1

DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@Data)

WHILE (@FoundIndex>0)
BEGIN
    INSERT INTO @Values (data)
    SELECT 
        Data = LTRIM(RTRIM(SUBSTRING(@Data, 1, @FoundIndex - 1)))
    SET @Data = SUBSTRING(@Data,
            @FoundIndex + DATALENGTH(@Delimeter) / 2,
            LEN(@Data))
    SET @Iterator = @Iterator + 1
    SET @FoundIndex = CHARINDEX(@Delimeter, @Data)
END
INSERT INTO @Values (Data)
SELECT Data = LTRIM(RTRIM(@Data))

SELECT
    usr.ID,
    CASE ISNULL(val.Data, -1)
        WHEN -1 THEN 'False'
        ELSE 'True'
    END
FROM @Users usr
    LEFT JOIN @Values val ON val.Data = usr.ID

Hopefully this gives you enough to get started, if a TVF is the way you want to go :)

P.S. There are lots of ways to do this and I'm sure some are better than the one presented..this works and if the field is only varchar(255), then the efficiency should be fine.