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, stackoverflow.com/questions/4021507/…, one of the answers has a tool you could install — chris85
– chris85, Commented Jul 28, 2017 at 12:14

Wiktor Stribiżew · Accepted Answer · 2017-07-28 12:06:18Z

3

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.

answered Jul 28, 2017 at 12:06

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 REGEX occurence and extraction in string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related