Mysql REGEX occurence and extraction in string

Question

I have a table MY_TBL with a varchar field MY_FLD. I need to find rows where MY_FLD contains a number followed by a hyphen followed by a number (typically a range).

The following lines should be selected:

fffff 1-5 fdsfdsfds
1-5 fdsfdsfds 
aaaa 10-23
1-50 fdsfdsfds

and these should not:

-5 dsgdgf
10- rere
-15
10 -23
10- 23

The regex for the number-hyphen-number pattern is \d+\-\d+.

How do I use it in a MySql statement? Is it possible to extract the pattern into a separate string?

fffff 1-5 fdsfdsfds  ->  1-5
1-5 fdsfdsfds  ->  1-5
aaaa 10-23  ->  10-23
1-50 fdsfdsfds  ->  1-50

You will need to return the full row then pull the value with a regex in PHP. Here's a thread on extracting with mysql, https://stackoverflow.com/questions/4021507/mysql-use-regex-to-extract-string-select-regex, one of the answers has a tool you could install — chris85, Jul 28 '17 at 12:14

score 3 · Accepted Answer · answered Jul 28 '17 at 12:06

You may use this to retrieve the matching rows:

SELECT * FROM MY_TBL WHERE MY_FLD REGEXP '[0-9]+-[0-9]+'

No need to escape the - and to match a digit, use [0-9] bracket expression.

There is no easy, built-in way to extract the regex matches in MySQL though.

Mysql REGEX occurence and extraction in string

1 Answers1