How to Escape Sequences in MySQL

1. Definition of Escape Sequence
2. Escape Sequences in MySQL
3. Escape Sequence With Wild Card Character in MySQL
4. Conclusions

In this article, we will learn about escape sequences. We’ll see its definition with examples and sample code.

We will also explore how to use it with wild card characters to find patterns in the data.

Definition of Escape Sequence

Escape sequence characters are non-printable characters that specify an alternative interpretation of the following character is the escape character sequence.

It starts from the backslash (represented as *\\*) and has two or more characters. For example, \n shows a new line where backslash is one character, and n is the second.

The list of escape sequences and their representation is given below.

While writing the application program, there are some situations where you have to manipulate string. This string must be properly escaped before saving it into the database.

Here we use escape sequences. As an example, if you want to INSERT a record in the customer table where the customer_firstname is Nyy'a, you must use an escape sequence.

Customer Table:

Order Table:

Example Code:

INSERT INTO customer(customer_firstname, customer_lastname, customer_age, customer_salary)
VALUES
('Nyy\'a', 'Daniel', 19, 10000);

Output:

Escape Sequences in MySQL

There are different escape sequences that are used in MySQL. See the following examples to understand.

New Line Example Code:

SELECT 'Hi!\nWelcome to the learning Escape Sequences.'

Output:

Hi!
Welcome to the learning Escape Sequences.

Carriage Return Character Example Code:

SET @strInput = 'Hello,How are you';
SET @strResult = REPLACE(@strInput, ',', CHAR(10)); #CHAR(10) represents \r
SELECT @strResult;

Output:

Hello
How are you

Question Mark Example Code:

SELECT 'Is this a question mark example\?';

Output:

Is this a question mark example?

Quotation Mark Example Code:

SELECT 'firstname', 'first\'name', '"firstname"',"firstname", '\"firstname\"','firstname\?';

Output:

Escape Sequence With Wild Card Character in MySQL

Wild card characters are used to get the desired pattern from the data and substitute one or more strings.

It is used with the LIKE operator, and the LIKE operator is used in the WHERE clause. Using an escape sequence with a wild card makes the job easy to get a certain pattern.

Example Code:

SELECT customer_firstname from customer where customer_firstname like '___\'_';

In this code, we are looking for the customer_firstname from the customer table where we have three characters before a single quote and one character after that. One underscore (_) is used for one character.

In ___\'_', we have three underscores to get three characters, then one single quote, and then one character at the end. See the following output to compare.

Output:

What if you are looking for a certain pattern in a column? Let’s practice it by using the order table. We will find all the order dates from the order_date field that contains the pattern as -12.

Here % shows one or more characters. It means one or more characters before the required pattern and one or more in the following example code.

Example Code:

SELECT order_date from order where order_date like '%\-12%';

Output:

To understand the example of double quotes with wild card characters, INSERT a new record in the customer table.

Example Code:

INSERT INTO customer(customer_firstname, customer_lastname, customer_age, customer_salary)
VALUES
('\"Nyy\'a\"', 'Dan\'iel', 19, 10000);

Output:

Use the following command to find the customer_firstname and customer_lastname from the customer table that meets the following pattern.

Example Code:

SELECT customer_firstname, customer_lastname from customer 
where customer_firstname like '%\"%\'%\"'
AND customer_lastname like'%\'___';

Output:

Conclusions

This article concluded that escape sequences are non-printable that specify alternative representation on the following character.

The strings must be escaped before getting saved in the database. We also learned that the escape characters are used with wild card characters to find different patterns.