Search code examples
regexrlike

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

Solution

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