I welcome everyone, there was a problem

there is a field in the database with approximately this content (this field is called a valid value, in the example one field)

2222 2223 2224 22 2

It is necessary to find an entry in which there is an entry, for example, 22, on the basis of this we understand that the search for LIKE '' or LIKE '% ...%' does not fit, you need an exact match of the string, 2222 does not fit, specifically suits

Less working option

 SELECT * FROM `table` WHERE `value` RLIKE '[[:<:]]22[[:>:]]'
  • why not like? You can safely specify carriage transfers there. In general, it would be necessary to normalize the table, to take out the plural field as a separate table, so that there would be one value per line. - Mike
  • @Mike if like then because there will be no exact match, if like%% then it will find extra records - MAX GRIM
  • will not be if the carriage transfers indicate - Mike
  • it would be better, of course, to make the list separated by commas, rather than in separate lines, and would be looking for the standard function find_in_set - Mike
  • @Mike is possible in more detail about "carriages"? - MAX GRIM

2 answers 2

If we are not talking about efficiency, then you can do something.

Option 1:

 WHERE CONCAT('\n', value, '\n') LIKE '%\n22\n%'

Option 2:

 WHERE FIND_IN_SET('22', REPLACE(value, '\n', ','))

Option 3:

 WHERE value RLIKE '[[=\n=]]22[[=\n]]'

There is a lot more to be thought of ... but it is better to normalize the data.

  • Option 3 returns error # 1139 - Received error 'brackets ([]) not balanced' from the regular expression - MAX GRIM
  • @MAXGRIM It works fine for me ... I don't even know who to blame for not specifying the version of the server. - Akina
  • I did not think that the server version is important, version - 5.7.16-10log - MAX GRIM
  • it started as WHERE value RLIKE '[[= = n]] 22 [[= = n]]', but returned an empty result - MAX GRIM
  • none of the options work on a little bit difficult tasks - MAX GRIM

If I correctly understood that the record of your type is not the whole table, but specifically one field of one record from it, then it seems so possible) (dare not laugh)

 SELECT * FROM `table` WHERE `value` RLIKE '(^22\\s)' OR `value` RLIKE '(\\s22$)' OR `value` RLIKE '(\\s22\\s)' OR `value` RLIKE '(^22$)'
  • And if these are all records of this field from the entire table, then so: SELECT * FROM table WHERE value LIKE '22' - Timur Tarverdiev