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 '=';

Answer 2

You don't need escape in this particular query but if you ever do (i.e. if you have the % character in your search term) you will need a escape character to differentiate between the % which is part of your search term and other % characters that serve as placeholder in the like part of the query.

In that case, you escape the % in your search term with the character you defined using the escape keyword.

For instance, say you want to look for the string 25% in the name field. You'll go like this:

WHERE  p.name LIKE '%25!%%' escape '!'