Dev Dev - 1 year ago 62
MySQL Question

MySQL query to find matching string using REGEXP not working

I am using

MySQL 5.5

I have a table named
, having a column

Some of the values in
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
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 then it seems matching.

Can anyone help me?

Answer Source

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, 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download