Select in SQLite filtering a String column with separator like array

Question

I have to add a column on my users table to filter by personalized tags. The idea is to store them in my sqlite database separated by ',' or ';' or some kind of separator.

How could I make a select filtering by those values?

The values of that column can change, so I need a method that enables adding or editing those tags in other queries.

Something like

SELECT * 
FROM users
WHERE tags contain especific tag or tags

or are there any better practice for doing this? (that don't require a new index table)

Answer 1

The idea is to store them in my sqlite database separated by ',' or ';' or some kind of separator

It looks like it's not too late yet not to hurt yourself... Before you go further, consider reading this advice.

The correct way to represent that 1-N relationship in your relational database is to create a separated table, eg user_tags, whose structure should look like:

user_tags
    id       -- unique identifier (optional)
    user_id  -- reference to users(id)
    tag      -- name of the tag

Possibly, you could enhance this model by creating a table to store the name of the tags (an other meta information if needed), and reference the id of the table in the user_tags table, whose structure would look like:

tags
    id       -- unique identifier
    name     -- tag name

user_tags
    id       -- unique identifier (optional)
    user_id  -- reference to users(id)
    tag_id   -- reference to tags(id)

I need a method that enables adding or editing those tags in other queries.

Using a comma-separated list, this will be painful. With a separate table, you can use simple joins for this, or an exists condition to check for multiple tags:

-- get all users who own at least one of these tags
select u.*
from users u
where exists (
    select 1
    from user_tags ut
    inner join tags t on ut.tag_id = t.id and t.name in ('foo', 'bar', 'baz')
    where ut.user_id = u.id
)