I'm trying to use RLIKE to match where a string of data is followed by either a specific character or an end of string ($). I get the expected results using just the end of string character ($), or just the expected character, or indeed any set of expected characters inside square brackets, but as soon as I go into square brackets for an expected character OR and end of string character, the end of line is not being matched.
Here is an example:
SQL data:
CREATE TABLE test_table (id int auto_increment primary key, search_string varchar(8));
INSERT INTO test_table (search_string) VALUES("123456789");
INSERT INTO test_table (search_string) VALUES("1234567");
INSERT INTO test_table (search_string) VALUES("123456");
INSERT INTO test_table (search_string) VALUES("12345");
INSERT INTO test_table (search_string) VALUES("12345E");
Example queries on this data:
SELECT count(*) FROM test_table WHERE search_string RLIKE "56[7]";
# the above returns fine - 2 rows (first and second)
SELECT count(*) FROM test_table WHERE search_string RLIKE "56[7YE]";
# the above returns fine - 2 rows (rows 2 and 5) as expected
SELECT count(*) FROM test_table WHERE search_String RLIKE "56$";
# the above returns fine - 1 rows (the third) as expected as 6 is followed by end of string
SELECT count(*) FROM test_table WHERE search_string RLIKE "56[7$]";
# the above returns only 1 row and should be 2 (rows 2 and 3 - '56' should be followed by a 7 or end of string)
Is there a special way to treat the $ character when it is in square brackets?
The regex may just need a slight tweak. Instead of 56[7$] instead you should use one of the following
56($|7)
56($|[7YE])
Within [], the $ is attempting to match a literal dollar sign. You are instead looking for $ to match an end of line and so it can't be within square brackets.