MySQL selects column row with LIKE or REGEX based on delimited substring

Question

I need to compare a column value with user input numbers. The row in the column is in the format 8|5|12|7|

Now I need to compare the input values ​​of user 2 , 5 , 3 with this column value

When I use the LIKE operator as '%2|%' , I get the result by matching the value of the |12|

How to match string using Regular Expression or any other way?

Answer 1

If I understand the question correctly, to make sure that you get 2|.. or ..|2|.. or |2 , you need to add sentences or

 where col like '%|2|%' or col like '2|%' or col like '%|2' or col='2' 

Answer 2

Something similar to this for testing 2 in this example 12|8|12|5|12|7|2|12|22

 # (^|\|)2(\||$) # # # Match the regex below and capture its match into backreference number 1 «(^|\|)» # Match this alternative (attempting the next alternative only if this one fails) «^» # Assert position at the beginning of the string «^» # Or match this alternative (the entire group fails if this one fails to match) «\|» # Match the character "|" literally «\|» # Match the character "2" literally «2» # Match the regex below and capture its match into backreference number 2 «(\||$)» # Match this alternative (attempting the next alternative only if this one fails) «\|» # Match the character "|" literally «\|» # Or match this alternative (the entire group fails if this one fails to match) «$» # Assert position at the end of the string, or before the line break at the end of the string, if any (line feed) «$» 

REGEXP "(^|\|)2(\||$)"

This allows you to use a column value of only 2 or 2|anything or anything|2 or the first thing|2|end thing .

Answer 3

When looking at column design, one of the ways you can do is LIKE '%|2|%'

Answer 4

Bad design for creating "arrays" in a cell. Use a separate table.

In any case, FIND_IN_SET() is a function that makes work much easier than a regular expression. (But you must use ',')