Value Comparison with a multivalued column in SQL Database Table
- by Rishabh Ohri
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
