CODEWITHSUNDEEP
mysqlregexcomparison
Norse
Norse

Reputation: 5757

MySQL REGEXP integer comparison

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?

Upvotes: 2

Views: 2084

Answers (1)

Tim Pietzcker
Tim Pietzcker

Reputation: 336108

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}.

Upvotes: 3

Related Questions