Negation of certain trailing sequences of strings in rlike regex
I want to match any string that doesn't end with "CA" or "CA [any number]". How can I do this using rlike in MySQL? (Note that it doesn't support ?! Etc.).
Here's a 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 ...
)
source to share
The trick turns it into a description of what you want to match. Something doesn't end with CA or CA (digit) when:
It ends with something other than an A or a digit, or
It ends with something other than A followed by 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 is equal to A followed by a digit, or
It is equal to A or a digit, or
Empty.
So:
rlike '[^A0-9]$|[^A][0-9]$|[^C]A[0-9]$|[^C]A$|^A[0-9]$|^[A0-9]$|^$'
Untested, not "optimized", possibly at least one bug somewhere higher.
source to share
Well the regex comes from a column in a table and most of the patterns work there already. So it's easiest if I could make this circuit work within the existing structure.
I did something like this once, what I did was create another column (I think I was using a bitfield) that had regex options (case insensitive, anchor, negation, etc.). A similar approach might work for you.
source to share