WHERE over a varchar field with multiple Id's

Question

I need make a query on SQL Server 2005 to match this scenario, I have a varchar(max) field with some id's like: "12323,12345,123,434345,2324,1211212" and the user will give me a group, the first 3 numbers; So, in where clause I'll have to match each of this id separate by a comma, any suggestion? How can I do this?

product | id
prod01    1212,211,3434342,54532
prod02    323,323,424,5435,35345
prod03    2323,1212

@UserEntry='123' 

Answer 1

You need a function to split the strings into separate values. Here's one. I don't know who wrote it originally, but it's been in our library since forever:

CREATE FUNCTION dbo.split_string (@v_string VARCHAR(2000), @v_delimiter VARCHAR(1))
    RETURNS @output TABLE (id INT IDENTITY NOT NULL, item VARCHAR(2000))
BEGIN
    DECLARE @v_item VARCHAR(2000);
    WHILE CHARINDEX(@v_delimiter, @v_string, 0) <> 0
    BEGIN
       SET @v_item   = RTRIM(LTRIM(SUBSTRING(@v_string, 1, CHARINDEX(@v_delimiter, @v_string, 0) - 1)));
       SET @v_string = RTRIM(LTRIM(SUBSTRING(@v_string, CHARINDEX(@v_delimiter, @v_string, 0) + LEN(@v_delimiter), LEN(@v_string))));
       IF LEN(@v_item) > 0
       BEGIN
           INSERT INTO @output SELECT @v_item;
       END;
    END;

    IF LEN(@v_string) > 0         -- catch the last item
    BEGIN
        INSERT INTO @output SELECT @v_string;
    END;

    RETURN
END

This will return the separate values as a table variable, from which you can use IF @UserEntry IN ...

Answer 2

You should change the data structure to have a junction table. This is a table with one row per product and id. Storing lists in strings is really, really bad. Storing integers in strings adds insult to injury.

But, sometimes you are stuck with someone else's bad decisions. If so, you can use like:

where ',' + id + ',' like '%,' @UserEntry + ',%'

However, this is a last resort and such queries cannot make use of an index. Fixing the data structure is the best option, by far.