Geek asks and answers

How to query SQL strings NO (array values) in a field with values ​​separated by commas?

I have an SQL table that looks like this:

id_question (int) | tags (varchar)
where the "tags" field is located - empty: NULL
- either filled with a single value (example: 1) (non-numeric)
- or filled with several values ​​separated by commas (for example: 273,2308,24) (non-numeric)

id_question (int) | tags (varchar)
1 | 1,373
2 | 283.4555,308,12
3 | 283.25.3

I have an array of blacklisted_tags. I would like to get id_questions of all questions whose tag field does not matter in the $ tags_blacklist blacklist.

For example:
$ tags_blacklist = array (1,3)
=> I should get 2
and not 1, because in the tag field 1 and not 3, because in the tag field 3.

What should my SQL query look like?

+3
source share
2 answers

Your database design violates Normalization Law # 1: NEVER START MESSAGES RELATED TO COMM.

Instead, you should:

  • id | Tag
  • 1 | 1
  • 1 | 373
  • 2 | 283
  • 2 | 4555
  • 2 | 308

and etc.

Thus, your request becomes as simple as

SELECT DISTINCT id 
FROM YourTable
WHERE tag NOT IN (1, 3)
+5
source

first sentence, change your tables to the following:

question
---------
id

question_tag
------------
question_id
tag

blacklist
----------
tag
+1
source

More articles:


All Articles