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?
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'
12|5|8 it will not return anything. Notice there is no % before 2 in second line.
tbl_profile_more WHERE interests LIKE '%2|%' " i am getting the output with the column 8|6|12|
2 this won't match, however if the value is 2| it will. Add another OR matching the value 2 to correct this - if needed.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.
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 ',')
colcolumn is storing data in an unnormalized format of8|5|12|7. I would recommend that you normalize your database. Also, your question is a bit unclear.'|' + your_column + '|' LIKE '%|2|%' or '|' + your_column + '|' LIKE '%|5|%' or '|' + your_column + '|' LIKE '%|3|%'etc.? that way it will match only the exact numbers you want inside the | characters.