MySQL: What does means "escape '!'" on query

Question

Editing someone else's code found this query:

SELECT c.name AS category_name,
       p.id,
       p.name,
       p.description,
       p.price,
       p.category_id,
       p.created
FROM   products p
       LEFT JOIN categories c
              ON p.category_id = c.id
WHERE  p.name LIKE '%keyword%' escape '!'
        OR p.description LIKE '%keyword%' escape '!'
ORDER  BY p.name ASC
LIMIT  0, 6

I understand everything but the escape '!' on lines 11 and 12. I guess is something related to 'escaping' and, in case of, don't know if is better implementing it before the query (code soup is PHP) or let the job to the DB engine (And what means the '!' symbol?).

Thanks in advance.

Answer 1

The ESCAPE keyword is used to escape pattern matching characters such as the (%) percentage and underscore (_) if they form part of the data.

Let's suppose that we want to check for the string "67%" we can use;

LIKE '67#%%' ESCAPE '#';

If we want to search for the movie "67% Guilty", we can use the script shown below to do that.

SELECT * FROM movies WHERE title LIKE '67#%%' ESCAPE '#';

Note the double "%%" in the LIKE clause, the first one in red "%" is treated as part of the string to be searched for. The other one is used to match any number of characters that follow.

The same query will also work if we use something like

SELECT * FROM movies WHERE title LIKE '67=%%' ESCAPE '=';