MySQL query to find matching string using REGEXP not working

Question

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?

Replace \d with [0-9] and \D with [^0-9] and \s with [[:space:]]. Try WHERE serving_data REGEXP '([0-9]+[[:space:]])?[0-9]+/[0-9]+[[:space:]]+cup';. See regex101.com/r/sO8fL3/1 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 25, 2016 at 13:25
Do not use /.../g. Use the pattern only. BTW, are the \ really in the string, or not? I believe there are no \s, right? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 25, 2016 at 13:29
SELECT id,name,nutrition_data,serving_data FROM nutritions WHERE serving_data REGEXP '[0-9][[:space:]]/[^0-9][0-9][[:space:]]cup'; - Seems not working. Yes \ is there in the string. — Dev
– Dev, Commented Aug 25, 2016 at 13:31
If \ is there, you need REGEXP '([0-9]+[[:space:]]+)?[0-9]+\\\\/[0-9]+[[:space:]]+cup'; See regex101.com/r/sO8fL3/2 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 25, 2016 at 13:34
It seems working but the records like [{"label":"1\/2 cup","unit":"cup"},{"label":"1 cup","unit":"cup"}] are also gets selected with the said pattern. I want only records like 1 3\/4 cups get selected with pattern. — Dev
– Dev, Commented Aug 25, 2016 at 13:44

Wiktor Stribiżew · Accepted Answer · 2016-08-25 13:51:36Z

4

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.

edited Aug 25, 2016 at 13:51

answered Aug 25, 2016 at 13:48

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query to find matching string using REGEXP not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related