Alby Alby - 2 months ago 7
MySQL Question

MySQL: Regular Expressions & Wildcards

In a MYSQL table I have a multiple text entries with the field of

Not Valid After: Dec 02 19:40:51 2016 GMT

What I'm trying to do is use REGEXP to find these entries and ignore anything which is less than Dec 31 23:59:59 2016 GMT. What is the best way to go about this?

Example: SEARCH_STRING where TABLE_COLUMN REGEXP "Not Valid After: blah blah" is less than Dec 31 23:59:59 2016 GMT.

I know that example is a little lacking in proper syntax, but I'm sure you get the gist of what I'm getting at. How do I take a set date, compare it to what's listed in a specific string buried in the details of larger text, and ignore anything between now() and that preset time being the end of the year?

Answer

I'd probably normalize the DB so the column was just a date, then you could use standard comparison functions (<=). Alternatively you could use a regex that will match the 4 year integers. Something like:

Not Valid After .* (20([0][0-9]|1[0-6])|1[0-9]{3}) GMT"

in mysql it'd be used as:

COLUMN not REGEXP "Not Valid After .* (20([0][0-9]|1[0-6])|1[0-9]{3}) GMT"

Example: http://sqlfiddle.com/#!9/26b735/4

You'll need to increase the character class, 1[0-6] as time passes; for next year 1[0-7].