regex to mysql regex

Question

I am searching for a way to replace and update a string value in MySQL starting or ending with a percentage.

I would like values like the following:

'15 % flour'
' 15 % beef'
'15% pork'
' 15 % rice'
'eggplant 15 %'
'banana 15 % '
'turkey 15%'
'chicken 15% '

In all these examples I would like to end out with only the ingredient. Ex: flour or beef

I found out that this will remove the percentage if in the starting of the line. REGEXP_REPLACE(ingredient_name, '^([0-9]|100)+(\%)+', '') But not if there is space between the number and the percentage symbol.

This was my best try, but I found out it wasn't compatible with Mysql regexp_replace.

\d+((' '|\s|\b)\%(' '|\s|$))

test link

Try ' *[0-9]+ *% *' or '[[:space:]]*[0-9]+[[:space:]]*%[[:space:]]*'. What is the MySQL version? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Mar 1, 2019 at 9:32
Try replacing ^[^a-zA-Z]+|[^a-zA-Z]+$ or ^[^[:alpha:]]+|[^[:alpha:]]+$ with empty string to see if this regex is supported in your mysql version. What's your mysql version though? — Pushpesh Kumar Rajwanshi
– Pushpesh Kumar Rajwanshi, Commented Mar 1, 2019 at 9:39
Both [[:space:]]*[0-9]+[[:space:]]*%[[:space:]]*, [[:blank:]]*[[:digit:]]+[[:blank:]]*%[[:blank:]]* and ^[^a-zA-Z]+|[^a-zA-Z]+$ seems to work. Whats the difference? — Sven Rishede Philipsen
– Sven Rishede Philipsen, Commented Mar 1, 2019 at 9:48
^[^a-zA-Z]+|[^a-zA-Z]+$ < This one removed national special characters in my ingredients. Therefore [[:space:]]*[0-9]+[[:space:]]*%[[:space:]]* seems to be the best solution. — Sven Rishede Philipsen
– Sven Rishede Philipsen, Commented Mar 1, 2019 at 9:57

Wiktor Stribiżew · Accepted Answer · 2019-03-01 10:01:06Z

1

You may use [[:space:]]* to match 0 or more whitespace chars between the number and %:

'[[:space:]]*[0-9]+[[:space:]]*%[[:space:]]*'

See the regex demo.

It matches

[[:space:]]* - 0 or more whitespace chars
[0-9]+ - 1 or more digits
[[:space:]]* - 0 or more whitespace chars
% - a % char
[[:space:]]* - 0 or more whitespace chars

Note that neither \d nor \s and \b might not be supported if your MySQL version is older than 8.x.

answered Mar 1, 2019 at 10:01

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

regex to mysql regex

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