Negating specific line end sequences in rlike regex
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 ...)
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.
Untested, un-"optimized", probably at least one error somewhere in the above.