ESCAPE

MySQL

Example

SELECT *
FROM Employees
WHERE city LIKE '%\_r%' ESCAPE '\';

Output

| Employee_Id | First_Name | Last_Name | City      |
|-------------|------------|-----------|-----------|
|    1        | John       | Smith     | New York  |
|    3        | James      | Johnson   | Marrakech |

Explanation

In this example, the SQL query uses the ESCAPE keyword to specify the escape character (\). It helps in searching for a pattern that includes an underscore (_). The underscore is usually a special character that matches any single character in SQL pattern matching, but with the \ escape character before it, it’s treated as a literal underscore. So LIKE '%\_r%' matches any string containing ‘_r’.

SQL Server

Example

SELECT column_name
FROM table_name
WHERE column_name LIKE '%25!_!%' ESCAPE '!';

Output

It will display the rows where column_name contains ’%!_!%’.

Explanation

The ESCAPE statement in SQL Server is used to escape special characters in a LIKE operator pattern. In the above example, the ESCAPE clause specifies ’!’ as the escape character. Thus, ’_!’ is considered as a normal underscore character, not a wildcard single character placeholder.

Oracle

Example

SELECT * FROM students WHERE name LIKE '%!%%' ESCAPE '!';

Output

NAME   AGE   GRADE
%John%  14  8

Explanation

The query searches for students whose names contain %. Here, the ESCAPE '!' clause allows the % to be interpreted normally and not as a wildcard character. The ! serves as an escape character for the %. So, any % preceded by ! in the LIKE pattern matches literal % in the students’ names.

SQLite

Example

SELECT EmployeeName FROM Employees WHERE EmployeeName LIKE '%\_%' ESCAPE '\';

Output

EmployeeName
------------
Anna_Smith
John_Doe\_Miller

Explanation

In this SQL command, the “ESCAPE” keyword is used to escape the underscore special character in the LIKE clause. Here, underscore is preceded by a backslash (\_), indicating that it should be treated as a literal character and not a wildcard. The “ESCAPE ”” clause at the end of the statement defines that the backslash (\) is to be used as the escape character.