CODEWITHSUNDEEP
mysqlregex
Dev
Dev

Reputation: 6720

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?

Upvotes: 3

Views: 1276

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627488

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.

Upvotes: 4

Related Questions