0

I have this table and want to select rows that contain exactly "22".

id   field
1    22
2    22,24,78
3    1,22,347
4    2,21,22
5    22,222

Select above rows, not below.

6    222
7    21,23
8    220,322
potashin
  • 44,205
  • 11
  • 83
  • 107
mitra razmara
  • 745
  • 6
  • 10
  • Then take a look at a beginners Database Design tutorial and refactor you database. Comma delimited lists of keys add nothing but complexity to the SQL you will have to write to process them – RiggsFolly Dec 10 '18 at 11:16

2 Answers2

4

The REGEXP operator comes in handy here:

SELECT *
FROM yourTable
WHERE field REGEXP '[[:<:]]22[[:>:]]';

We can also try using FIND_IN_SET:

SELECT *
FROM yourTable
WHERE FIND_IN_SET('22', field) > 0;

If all else fails, we can use LIKE, but it takes slightly more heavy lifting:

SELECT *
FROM yourTable
WHERE CONCAT(',', field, ',') LIKE '%,22,%';

But in general, it is bad practice to store CSV (comma separated values) in your database tables. It would be better to store each field value on a separate rows, e.g. use this:

id   field
1    22
2    22
2    24
2    78
...
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

select * from where field like '%22%';

Channa
  • 742
  • 17
  • 28