Escaping String literals in REGEXP with MySQL

Question

Have a tricky problem down here.

Lets say I have table 'books', which has column 'title'. And I have one row with 'title' = 'MyBook'.

I want using REGEXP search for book title and I want to quote all searchable string to literal sequence:

SELECT * FROM books WHERE title REGEXP '\\QMyBook\\E';

When I work with mariaDB my query works fine. DB recognises this quotation marks. But when I work with plain MySql this query returns nothing. The problem is not in backslashes, I think MySql have troubles to work with "\Q" and "\E". Maybe there is a way to do my goal in other way, but I realy don't want to escape every character separately.

I execute this query from java. But plain query from console works the same way

The problem is - it is really necessary to use REGEXP (not LIKE) operator because of some reasons.

But I don't want that query with regexp special characters (for example 'My.ook') work here. I need that any string, with or without regexp special symbols, processed by mysql as literal sequence(if we pass 'dot' - we are looking for 'dot', not for 'any character')

I don't think MySQL supports the \Q...\E escape sequence, see the documentation. If you can provide an explanation what you want to do, I might be able to help further. — Tom
– Tom, Commented May 6, 2015 at 12:44
We have a list of search-types in our application. There is at least one case, where we can't use 'LIKE' - when we searching for full word(we have a complex regex pattern which meets our requirements. So, we must use REGEXP but not allow its special characters) — maret
– maret, Commented May 6, 2015 at 12:56
Also 'LIKE' has a problem with '%' and '_'. This symbols must be escaped — maret
– maret, Commented May 6, 2015 at 12:57
I don't think this is possible in MySQL. You can however write your own php addslashes() method in java. Just put slashes in front of every special character you want escaped. Other than that I can't think of any other workarounds. — Tom
– Tom, Commented May 6, 2015 at 13:12

santiago arizti · Accepted Answer · 2016-07-28 00:32:02Z

-1

I don't think this can be done with plain MySQL, but if you upgrade to MariaDB you can use the syntax you want.

Please refer to the documentation of MariaDB for more information: https://mariadb.com/kb/en/mariadb/pcre/#quoting

SELECT * FROM books WHERE title REGEXP '\\QMyBook\\E';

I recommend to switch to MariaDB for the following reasons:

Everything that used to work in MySQL works in MariaDB
You can connect from any client to MariaDB without changing anything from your MySQL configuration (It trully is a drop-in replacement of MySQL)
MariaDB is not owned by a big company that might want to charge for features in the future
MariaDB comes with a lot of enhancements, including a lot more REGEXP features, like the one you want and REGEXP_REPLACE, among other improvements.

This list is not exhaustive.

answered Jul 28, 2016 at 0:32

santiago arizti

4,7973 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vitamon Over a year ago

The question was how to solve the problem for mysql, switching to maria is not an option for many

santiago arizti Over a year ago

that doesn't mean that is is not an option for some

Collectives™ on Stack Overflow

Escaping String literals in REGEXP with MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related