Search code examples
sqlsql-serverjoinsql-likesql-in

Using SQL, how do I convert a comma separated list to be used with the IN command?

Currently I have a table that has a user_type column and will only show the row if the user matches that user_type. I want to make it so there can be multiple user_types set without duplicating the data and without creating another table. I thought that I could convert the column from an int to varchar and have it be a comma separated list of user type IDs.

So far it has been working great. That is as long as I know what the user_type is beforehand because then I can specifically use that when checking if I should display to the user:

SELECT *
FROM perm
WHERE user_type='50'
    OR user_type LIKE '50,%'
    OR user_type LIKE '%,50,%'
    OR user_type LIKE '%,50'

The issue comes when I am trying to join tables on the value. When I tried using IN:

SELECT p.*
FROM perm p
JOIN [user] u ON u.type IN (p.user_type)

I get the error: Conversion failed when converting the varchar value '50,40,30' to data type int. So I decided I would go back to the LIKE method which I have been using above:

SELECT p.*
FROM perm p
JOIN [user] u ON (
    u.type LIKE p.user_type
    OR u.type LIKE (p.user_type + ',%')
    OR u.type LIKE ('%,' + p.user_type + ',%')
    OR u.type LIKE ('%,' + p.user_type)
)

This only returns results that have one user type value only. Is there a way to convert a comma separated list to be used with the IN command? Or is there any way to make a dynamic LIKE argument?

Solution

  • There'e many examples of TSQL code that split separated string into "array" or table variable.

    For example using this function: https://stackoverflow.com/a/10914602/961695, you can rewrite your query as:

    SELECT p.*
FROM perm p JOIN dbo.splitstring('50,40,30') s
ON p.user_type = s.name

    (and avoid dynamic SQL as well)