How to run select query on columns having comma separated values-CodePudding

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

CodePudding user response：

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 (...)

CodePudding user response：

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.