MySQL regexp for matching list of numbers in a string

Question

I have several rows with strings similar to this: 1,19|11|14,2
The info I want to look at is: 19|11|14 (which is a list of the numbers, 19 11 and 14)
This info should be matched to see if any of the numbers are in the range between 8 and 13

What's the most effective way to accomplish this? I tried using a regexp like:
[^0-9]*(8|9|10|11|12|13)[^0-9]*
But this would also match the number 9 which is actually 19.

Other methods for parsing the string is also welcomed, only functions available in MySQL 5.0 can be used.

Your Regex currently states 0 or more non-numerical characters followed by a number between 8 and 13 followed by 0 or more non-numerical characters. It's not clear what you are attempting to match?! — Peter O'Callaghan
– Peter O'Callaghan, Commented Jul 28, 2010 at 16:24
@Cags For example I would like to match the number 8 in 1,8|17,1 and not in 1,18|17,1. I would only like to match 8 if the preceding isn't a number — baloo
– baloo, Commented Jul 28, 2010 at 16:27

Peter O'Callaghan · Accepted Answer · 2010-07-28 16:38:17Z

3

From what I remember MySQLs Regex support is very simplistic I'm not sure how possible this will actually be. I don't believe it supports word boundaries or look around assertions. How about this...

(^|[^0-9])(8|9|10|11|12|13)([^0-9]|$)

answered Jul 28, 2010 at 16:38

Peter O'Callaghan

6,1863 gold badges28 silver badges27 bronze badges

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

1 Comment

Jcc.Sanabria Over a year ago

Finally, your proposed solution solved my issue. I'd tried others like ^[[:digit:]]{10}$ and '^[0-9]{10}$'. Only with yours I get the expected value :)

Collectives™ on Stack Overflow

MySQL regexp for matching list of numbers in a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related