Value Comparison with a multivalued column in SQL Database Table

Question

Hi All,

Suppose there is a table A which has a column AccessRights which is multivalued( Eg of values in it in this format STOLI,HELP,BRANCH(comma separated string)

Now a stored procedure is written against this table to fetch records based on a AccessRight parameter sent to the SP. Let that parameter be @AccessRights, this is also a comma separated string which may have a value like STOLI,BRANCH,HELPLINE etc

Now I want to compare individual values from the parameter @AccessRights with the column AccessRights.

Current Approach is I split the Comma Separated string(@AccessRights) using a User Defined Function Split. And I get Individual values in a Table variable(Contains only one column "accessGroup"), the individual values are in a Table variable under the column name accessGroup and I use following code in the SP for comparison

Where AccessRights like '%'+accessGroup+'%'

Now if the user passes the parameter (HELP, OLI) instead of( HELP,STOLI) the SP will give the output. What should be done for comparison so that that subststring OLI does not give the output for STOLI

Answer 1

Split both collections, store them on temporary #tables and join them.

Answer 2

Following trick should do it (self-documented):

WHERE (',' + AccessRights + ',') LIKE ('%,' + accessGroup  + ',%')

But ideally split both sides into tables tables and compare values.