How to run select query on columns having comma separated values

Question

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";

score 2 · Accepted Answer · answered Oct 03 '21 at 16:04

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.

score 1 · Answer 2 · answered Oct 03 '21 at 16:15

You're asking a question about a problem with the database design. The problem disappears when the design error is rectified.

Specifically, your database is not 1NF: not in First Normal Form. The department is not a scalar value, but represents a repeating group. 1NF requires all repeating groups be eliminated.

Normal forms were invented to help database designers define databases that can be fully exploited by SQL. In the case of repeating groups, SQL has no operator for the nth value in a group (especially not in SQLite, where the repeating group is just a string).

To normalize your table, create this:

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

Now your query can be written using existential quantification. In SQL, that takes the form of where not exists (...)

I understand the discrepancy in DB design, but unfortunately, my privilege is limited to SELECT operations only. What you pointed out is the right way. — Sid133, Oct 03 '21 at 16:33

How to run select query on columns having comma separated values

2 Answers2