MySQL select column string with LIKE or REGEX base

问题:

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

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

When I use LIKE operator as '%2|%' I got the output by matching with column value |12|

How do I match the string by using Regular Expression or any other way?

回答1:

If I understand the question correct, then to make sure that you get 2|.. or ..|2|.. or |2, you need to add or clauses

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

回答2:

Something similar to this to test for 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 for the column value to just be 2 or 2|anything or anything|2 or first thing|2|end thing.

回答3:

By looking your column design, one of the way u can do is LIKE '%|2|%'

回答4:

It is bad design to build "arrays" in a cell. Use a separate table.

Anyway, FIND_IN_SET() is a function that does the work a lot easier than a regexp. (But you have to use ',')