[SOLVED] How to run select query on columns having comma separated values

How to run select query on columns having comma separated values

I know how to separate comma-separated column values into different columns. However, my question is how to query these columns in a where condition. I have a sample table named 'Details' below.

    User      Department
    ----      ----------
    User1     Admin, Accounts
    User2     Admin
    User3     Admin, Finance

Suppose I want to query the user who is in an Admin but not in Finance. As you can see, User1 and User2 satisfy the condition, but User3 doesn't.

How can I build a select query to get the User3 as result

I know the 'like' operator can be used, but not sure how.

SELECT USER 
FROM DETAILS 
WHERE DEPARTMENT LIKE "Admin"||"Accounts" 
AND NOT LIKE "Finance";

Solution

Use the operator LIKE:

SELECT USER 
FROM DETAILS 
WHERE ',' || DEPARTMENT || ',' LIKE '%,' || 'Admin' || ',%'
  AND ',' || DEPARTMENT || ',' LIKE '%,' || 'Finance' || ',%'
  AND ',' || DEPARTMENT || ',' NOT LIKE '%,' || 'Accounts' || ',%';

Or the function INSTR():

SELECT USER 
FROM DETAILS 
WHERE INSTR(',' || DEPARTMENT || ',', ',' || 'Admin' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Finance' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Accounts' || ',') = 0;

This will work if there are no spaces after each comma in the column DEPARTMENT.

See the demo.