Query a semicolon-separated values column with WHERE IN

Question

I have the following problem:

    SELECT a.* 
      FROM programm a 
     WHERE a.traffic IN ('DE;AT;CH;') 
     ORDER 
        BY a.programmid asc 
     LIMIT 0,10

That works wonderfully.

But if I only want to query one country, it is not possible:

    SELECT a.* 
      FROM programm a 
     WHERE a.traffic IN ('DE;') 
     ORDER 
        BY a.programmid asc 
     LIMIT 0,10

Database structure:

<pre>
 programmid | traffic
------------+------
  1         | DE;AT;CH;
  2         | AT;
  3         | CH;
  4         | PL;BG;DE;  
  5         | AM;BG;DE;
</pre>

When I query the database,

    SELECT a.* FROM `programm` as a WHERE a.traffic IN ('DE;,BG;') ORDER BY a.programmid asc LIMIT 0,10

all program IDs should be listed where DE,BG is in traffic. That would be programmid 1,4,5

https://stackoverflow.com/questions/1258743/normalization-in-mysql the solution is not to build hacky code to accommodate bad table design, but to normalize your database so that you can write good clean code. — mickmackusa, Nov 20 '20 at 11:55

score 1 · Answer 1 · answered Nov 20 '20 at 11:47

Variant 1:

WHERE FIND_IN_SET('DE', REPLACE(traffic, ';', ','))

Variant 2:

WHERE LOCATE(';DE;', CONCAT( ';', traffic, ';'))

There are more variants. But all of them cannot use indices and causes full table scan.

Normalize your data.

PS. If you need to search by a lot of countries then search by each with separate expression.

Query a semicolon-separated values column with WHERE IN

1 Answers1