1

I'm trying to find mismatching regexp

Consider two tables

foo

Table with testvalues

test_value id
---------  --
10001      test

bar

Table with regexps

test_reg    id
--------    --
^([1-9]*)$  test

I try to compare each line vs each lines regexp

SELECT * FROM foo RIGHT JOIN bar ON foo.id=bar.id
    WHERE test_value NOT REGEXP CONCAT(bar.test_reg)

This seems to work (at least for values<=9999). But I have a weird issue. If a number jumps above 10001 I do get a match. If I replace the CONCAT(bar.test_reg) with CONCAT('^([1-9]*)$') it works as expected. I also tried a self_join with the same result.

SELECT * FROM foo RIGHT JOIN bar ON foo.id=bar.id
    AND test_value NOT REGEXP CONCAT(bar.test_reg)

I do guess that the error lies somewhere else (e.g. comparing not each line)

Rick James
  • 135,179
  • 13
  • 127
  • 222
blackswan
  • 204
  • 3
  • 16
  • 3
    `[1-9]` doesn't include `0`. Maybe you actually want `[0-9]`? – sticky bit Jan 30 '20 at 14:16
  • I actually tested aswell with ^(?:[1-9][0-9]*|0)$. Now it works, before it didn't... very strange. Thank you. – blackswan Jan 30 '20 at 14:24
  • Why `RIGHT`? Why `CONCAT`? – Rick James Feb 01 '20 at 18:51
  • I was under the impression that I kind of had to convert the text (regexp) to a variable to us it, which is not the case. (I was wrong there). RIGHT JOIN is inherited from the original SQL I'm using. (I haven't tried other variants as it suits my needs) – blackswan Feb 05 '20 at 20:12

0 Answers0