Dev Dev - 3 months ago 11
MySQL Question

MySQL query to find matching string using REGEXP not working

I am using

MySQL 5.5
.

I have a table named
nutritions
, having a column
serving_data
with
text
datatype.

Some of the values in
serving_data
column are like:

[{"label":"1 3\/4 cups","unit":"3\/4 cups"},{"label":"1 cups","unit":"3\/4 cups"},{"label":"1 container (7 cups ea.)","unit":"3\/4 cups"}]


Now, I want to find records containing
serving_data
like
1 3\/4 cups
.

For that I've made a query,

SELECT id,`name`,`nutrition_data`,`serving_data`
FROM `nutritions` WHERE serving_data REGEXP '(\d\s\\\D\d\scup)+';


But is seems not working.

Also I've tried

SELECT id,`name`,`nutrition_data`,`serving_data`
FROM `nutritions` WHERE serving_data REGEXP '/(\d\s\\\D\d\scup)+/g';


If I use the same pattern in http://regexr.com/ then it seems matching.

Can anyone help me?

Answer

Note that in MySQL regex, you cannot use shorthand classes like \d, \D or \s, replace them with [0-9], [^0-9] and [[:space:]] respectively.

You may use

REGEXP '[0-9]+[[:space:]][0-9]+\\\\/[0-9]+[[:space:]]+cup'

See the regex demo (note that in general, regex101.com does not support MySQL regex flavor, but the PCRE option supports the POSIX character classes like [:digit:], [:space:], so it is only used for a demo here, not as a proof it works with MySQL REGEXP).

Pattern details:

  • [0-9]+ - 1 or more digits
  • [[:space:]] - a whitespace
  • [0-9]+- 1 or more digits
  • \\\\/ - a literal \/ char sequence
  • [0-9]+[[:space:]]+cup - 1 or more digits, 1 or more whitespaces, cup.

Note that you may precise the word cup with a word boundary, add a [[:>:]] pattern after it to match a cup as a whole word.