Using a single value parameter to search a multi value comma separated string in SQL

Question

In my SQL query I have a single value parameter @MandatoryGroup which is returning an integer value for one of the ID numbers for a list like this

202  Group A
203  Group B
204  Group C
205  Group D

So the value of @MandatoryGroup could be 202

In my table of people, people are linked to groups by a column containing a comma separated list e.g. if a person is in groups A, C and D the value in the column TblPeople.PeopleCourseGroup = 202,204,205

I'm trying to work out how to find all records which contain group 202. The closest I can get without errors is shown below but this only gives me results where people are ONLY in group 202. A record with the value 202,204,205 would not be found.

SELECT     
TblPeople.PeopleId
,TblPeople.PeopleSurname
,TblPeople.PeopleForename
,TblPeople.PeopleCourseGroup

FROM 
TblPeople

WHERE 
CONVERT(Varchar(10),@MandatoryGroup) = TblPeople.PeopleCourseGroup

I've tried various combinations of IN and LIKE without success. web searches are bringing back lots of results about multi value parameters but I can't see anything relating to doing this the other way round.

Eils

Maybe create a table valued function that takes a comma separated VARCHAR and returns multiple rows. You could `CROSS APPLY` this to your table and join on the resultset. — Chris Pickford, Jun 03 '16 at 14:41

score 1 · Accepted Answer · edited May 23 '17 at 10:34

Edit:

I have to point out that this question describes a similar problem to what you have asked in: Using CASE in WHERE Statement when parameter has multiple values The answer (provided by @Juan Carlos Oropeza) to that question would also work for this question, specifically using the WHERE .. LIKE construct:

WHERE  ',' + PeopleCourseGroup + ',' LIKE '%,' + '202' + ',%'

My initial answer is below:

You need to create a string splitting function:

CREATE FUNCTION tfnSplit
(
    @Source VARCHAR( MAX ),
    @Delimitor VARCHAR( 100 )
)
RETURNS @Results TABLE( ID INT PRIMARY KEY, Value VARCHAR( MAX ))
AS
BEGIN
    DECLARE @CurrentPosition BIGINT, @NextPosition BIGINT, @DelimitorLength INT
    DECLARE @ID INT -- Performance improvement of 15% when ID is incremented manually

    SET @ID = 1
    SET @Source = @Source + @Delimitor
    SET @DelimitorLength = DATALENGTH( @Delimitor )

    SET @CurrentPosition = 1 
    SET @NextPosition = CHARINDEX( @Delimitor, @Source, @CurrentPosition )

    WHILE @NextPosition > 0
    BEGIN
        INSERT @Results( ID, Value )
        SELECT @ID, SUBSTRING( @Source, @CurrentPosition, @NextPosition - @CurrentPosition )

        SET @ID = @ID + 1
        SET @CurrentPosition = @NextPosition + @DelimitorLength
        SET @NextPosition = CHARINDEX( @Delimitor, @Source, @CurrentPosition )
    END

    RETURN
END;

And then CROSS APPLY it in your code:

 SELECT     
    TblPeople.PeopleId
    ,TblPeople.PeopleSurname
    ,TblPeople.PeopleForename
    ,TblPeople.PeopleCourseGroup
    FROM TblPeople
        CROSS APPLY dbo.tfnSplit( PeopleCourseGroup, ',' ) AS Groups
    WHERE Groups.Value = '202'

It is bad design to store relations as a coma delimeted list as you will have problems joining tables (as you have encountered). In your case you should create another table, say PeopleGroups to store that relationships:

CREATE TABLE PeopleGroups( PeopleId INT, GroupID INT )

You can optionally add foreign key constraints.

Using a single value parameter to search a multi value comma separated string in SQL

1 Answers1

Edit:

My initial answer is below: