Negate Regex in MySQL rlike Expression

Question

I want to match any line that does not end with 'CA' or 'CA[any number]'. How can I do that using rlike in MySQL? (Note it doesn't support ?! etc).

Here's the regex for a positive match, I just need a way to negate it: '^.*[C][A][0-9]?$'

(Due to an embarrassing architecture limitation, I don't want to use not rlike ...)

Answer 1

Well the regex is coming from a column in a table, and most of the patterns there already work. So it would easiest if I could make this pattern work within the existing structure.

I did something similar once, what I did was create another column (I think I used a bitfield) that contained options for the regular expression (case-insensitivity, anchoring, negation, etc). A similar approach might work for you.

Answer 2

The trick is turning it into a description of what you do want to match. Something doesn't end in CA or CA(digit) when:

It ends with something other than A or digit, or

It ends with something other than A, then a digit, or

It ends with something other than C, then A, then a digit, or

It ends with something other than C, then A, or

It equals A followed by a digit, or

It equals A or a digit, or

It is empty.

So:

rlike '[^A0-9]$|[^A][0-9]$|[^C]A[0-9]$|[^C]A$|^A[0-9]$|^[A0-9]$|^$'

Untested, un-"optimized", probably at least one error somewhere in the above.