sqlite select when values are stored in an array

hi
i have a table quotes that have a field quote_category which hold categories ids. meaning, this quote might belong to several categories.
i attach an image of the table.
for example: how can i select only quotes that the id 3 is in the quote_category array?
the database
LVL 1
derridaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
not to provide a solution at this stage, but is that possible to change your table design? Personally thought is that by putting all the relevant ids into a simple column is really a bad design.

Do you think can create a separate table to hold the ids instead?
derridaAuthor Commented:
hi
can you elaborate on that idea? maybe example?
Member_2_276102Commented:
One of the major points of relational databases is to avoid (or eliminate) exactly that kind of operation. So, you're now trying to use a relational language (SQL) to do one of the things that it was designed not to do. If you think about that, it should become clear why it's not easy.

The idea is that you'd create a "related" table that lists the quote_categorys for each quote_id and/or quote_author (whatever the "relation" is). You wouldn't try to put multiple values into a single column.

Once that's done, it becomes simple, as well as easier to modify for future needs.

There possibly are a couple ways to get it done with what you have. It's hard to be sure for a couple reasons. For example, are the values in quote_category supposed to be numeric? (They aren't; they're substrings of character values, possibly of varchar values.) Are they always single-character values? (Or might they be larger after a few years go by so that you might have "(1,5,10,12)" in that column someday? Hard to be certain about the future.)
Ryan ChongSoftware Team LeadCommented:
so your original table is shown as follows:

Table0:
quote_id
quote_author
quote_category
quote_text

to derive to become 2 tables, for example:

Table0:
quote_id
quote_author
quote_text

Table0
Table1:
id (PK)
quote_id
quote_category

Table1
and then we join them when necessary to get the results we wanted

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.