How do i escape a quote in mysql?

The string data are required to escape before using in the select query to return the result set with a single quote (”), backslash (\), ASCII NULL, etc. The MySQL QUOTE() function is used to do this task. It generates a string value that escapes the data of the query properly. The uses of the QUOTE() function to escape string in MySQL have been described in this tutorial.

Syntax:

The QUOTE() function takes a string value as the argument that will be escaped, and it returns the escaped string after executing the query. The syntax of this function is given below.

Escape Sequence Characters:

The uses of different escape sequence characters have been explained below.

Use of QUOTE() function for a string value:

The uses of the QUOTE() function with different types of escape sequence characters have been shown in this part of the tutorial.

Example-1: Use of QUOTE() function to print simple string

When the string value is printed without the QUOTE() function using the SELECT statement, the string value will be printed without any quote. Run the following SELECT statement with the QUOTE() function to print a simple string with a single quote.

SELECT QUOTE('Welcome to LinuxHint') AS Escaped_Value;

Output:

The following output will appear after executing the above query.

Example-2: Use of QUOTE() function to print single quote inside the string

Run the following SELECT statement with the QUOTE() function to print a string with the single quote.

SELECT QUOTE('MySQL''Database''Server' ) AS Escaped_Value;

Output:

The following output will appear after executing the above query.

Example-3: Use of QUOTE() function to print double quote inside the string

Run the following SELECT statement with the QUOTE() function to print a string with the double-quoted string.

SELECT QUOTE('"Linux" Hint') AS Escaped_Value;

Output:

The following output will appear after executing the above query.

Example-4: Use of QUOTE() function to print a string with the newline

Run the following SELECT statement with the QUOTE() function to print a string with the newline.

SELECT QUOTE('Linux\nHint') AS Escaped_Value;

Output:

The following output will appear after executing the above query.

Example-5: Use of QUOTE() function to print a string with tab space

Run the following SELECT statement with the QUOTE() function to print a string with the ‘\t’ character.

SELECT QUOTE('Hello\tWorld') AS Escaped_Value;

Output:

The following output will appear after executing the above query. The tab space has been generated between ‘Hello’ and ‘World’ in the output for using the ‘\t’ character.

Example-6: Use of QUOTE() function to print a string with the backspace.

Run the following SELECT statement with the QUOTE() function to print a string after applying a ‘\b’ character.

SELECT QUOTE('Linux\bHint') AS Escaped_Value;

Output:

The following output will appear after executing the above query. The character ‘x’ has been removed from the main string using the ‘\b’ character inside the string.

Use of the QUOTE() function for the table data:

You have to create a table with data in a MySQL database to check using the QUOTE() function on the table data. Open the terminal and connect with the MySQL server by executing the following command.

Run the following command to create a database named test_db.

Run the following command to select the database.

Run the following query to create a table named clients with five fields.

CREATETABLEclients(
id INTNOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
address TEXT,
contact_noVARCHAR(15));

Run the following INSERT query to insert 4 records into the clients table.

INSERT INTO `clients` (`id`, `name`, `email`, `address`, `contact_no`) VALUES

('4001', 'Laboni Sarkar', '[email protected]', '34, Dhanmondi 9/A, Dhaka.', '01844767234'),

('4002', 'Tahsin Ahmed', '[email protected]', '123/1, Jigatola, Dhaka.', '015993487812'),

('4003', 'Hasina Pervin', '[email protected]', '280, Shantibagh, Dhaka.', '01600487812'),

('4004', 'Mehrab Ali', '[email protected]', '78, Cox’s Bazar, Chottogram.', '01727863459');

Run the following query to print all records of the clients table.

Output:

The following output will appear after executing the above query.

Example-1: Use of QUOTE() function in a single field of a table.

When the table’s string value is printed using a SELECT query, the output will show the string value without any quote. The following SELECT query will print all records of the clients table and another extra column QUOTE (email) by enclosing the email field of the clients table with the single quote.

SELECT *, QUOTE (email) FROM clients;

Output:

The following output will appear after executing the above query.

Example-2: Use of QUOTE() function in multiple fields of a table.

The following SELECT query will print the original values of the name and address fields and the single-quoted values of the email and contact_no fields of the clients table.

SELECT name as Name, QUOTE (email) as Email, address as Address, QUOTE (contact_no) as `Contact No`

FROM clients;

Output:

The following output will appear after executing the above query.

Conclusion:

The QUOTE() function is used in the SELECT query to format the string data using different escape characters before printing. The ways of using the QUOTE() function for the simple string data and the table data have been shown in this tutorial to help MySQL users know how to escape the string in MySQL.