MySQL REGEXP integer comparison

Question

I'm using REGEXP to match specific strings out of a ton of text in a LONGTEXT column. For example:

Text text text text text
 text text text text text
 text text SOLD: 75/101 text
 text text text text text

So my queries have been looking like this:

SELECT * FROM `test` WHERE `file` REGEXP
'SOLD:[ ]{1}[0-9]{1,2,3}/[0-9]{1,2,3}'

Which would properly match the SOLD: 75/101 string.

But is it possible, to do a comparison on the numerator? Such as, find all SOLD: >=75/101?

The comparison number would be a user inputted number via $_POST. I know that REGEXP isn't really meant for comparing numbers, but is there some way to maybe capture the string with regexp, then perform a comparison on the integers some other way?

Would you only have to consider fractions like 76/101 and 123/101, or would you also have to take 70/80 into account (which is greater than 75/101, of course)? — Tim Pietzcker
– Tim Pietzcker, Commented Sep 23, 2012 at 21:09
@TimPietzcker Yes, I'd have to consider those first two also. I'm not interested in comparing the fraction as a whole, just the numerator. — Norse
– Norse, Commented Sep 23, 2012 at 21:13

Tim Pietzcker · Accepted Answer · 2012-09-23 21:46:22Z

3

If you only have to look at the numerator, as stated in your comment, then

SELECT * FROM mytable WHERE mycolumn REGEXP 
"SOLD: ([1-9][0-9]{2,}|[8-9][0-9]|7[5-9])/101";

should work.

By the way, [0-9]{1,2,3} is a syntax error in most regex flavors (I don't know how MySQL handles it); the correct way to specify a range is [0-9]{1,3}.

edited Sep 23, 2012 at 21:46

answered Sep 23, 2012 at 21:22

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

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

3 Comments

Norse Over a year ago

What is the user inputted $_POST value was 80 or 90 though?

Tim Pietzcker Over a year ago

Ah, right. I hadn't read that part thoroughly enough. Sorry, MySQL regexes don't support capturing groups, so I guess you really have to use a custom function for that. Unfortunately, I don't know MySQL well enough to suggest a solution.

Tim Pietzcker Over a year ago

@Norse: That means "2 or more".

Collectives™ on Stack Overflow

MySQL REGEXP integer comparison

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related