JIYIK CN >

In this article, we will learn about escape sequences. We will look at its definition with examples and sample code.

We'll also explore how to use it with wildcards to find patterns in your data.

Definition of escape sequences

An escape sequence character is a non-printable character that specifies that the alternative interpretation of the following character is the escape character sequence.

It starts with a backslash (denoted as *\\*) and has two or more characters. For example, \na new line is displayed where 反斜杠is one character and nis the second character.

Given below is a list of escape sequences and their representations.

When writing applications, there are situations where you have to manipulate strings. 数据库This string must be escaped properly before saving it to .

Here we use escape sequences. For example, if you want to have records in a table customer_firstnamewith , you have to use .Nyy'acustomerINSERTescape sequence

Customer table:

Order form:

Sample 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 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.

Example code for carriage return character:

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?

Quote sample code:

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

Output:

MySQL with wildcards转义序列

Wildcard characters are used to obtain the required pattern from the data and replace one or more strings.

It LIKEis used with the operator, LIKEwhich is used in WHEREthe clause. Using escape sequences with wildcard characters makes it easy to get specific patterns.

Sample code:

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

In this code, we customerlook up from the table customer_firstnamewhere there are three characters before the single quote and one character after the single quote. An underscore ( _) is used for one character.

In ___\'_', we use three underscores to get three characters, followed by a single quote, and finally a character. See the following output for comparison.

Output:

What if you want to find some pattern in a column? Let's orderpractice it using a table. We will find all the order dates from the field that contain -12the pattern .order_date

One or more characters are shown here %. It represents one or more characters before the required pattern and one or more characters in the following sample code.

Sample code:

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

Output:

To understand the wild card charactersdouble quotes example with , INSERT``customernew record in the table.

Sample 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 and customerfrom the table that match the following pattern .customer_firstnamecustomer_lastname

Sample code:

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

Output:

in conclusion

The paper concludes that an escape sequence is non-printable, it specifies an alternative representation on the following character.

Strings must be escaped before being saved to the database. We also learned that escape characters are used with wildcards to find different patterns.