MySQL QUOTE() function

QUOTE() function

MySQL QUOTE() produces a string which is a properly escaped data value in an SQL statement, out of a user supplied by the string as an argument.

The function achieves this by enclosing the string with single quotes, and by preceding each single quote, backslash, ASCII NUL and control-Z with a backslash.

If the string passed as argument is NULL, the function returns a word NULL.

This function is useful in -

• String representation: It allows you to generate a valid string representation by enclosing the input string in single quotation marks.
• Escaping special characters: QUOTE() automatically escapes special characters within the string, such as single quotes, backslashes, and certain control characters.

Syntax:

QUOTE(str)

Argument:

Syntax Diagram:

MySQL Version: 8.0

Example of MySQL QUOTE() function

The following MySQL statement returns a string 'w3re\'source'.

Code:

SELECT QUOTE('w3re''source');

Output:

mysql> SELECT QUOTE('w3re''source');
+-----------------------+
| QUOTE('w3re''source') |
+-----------------------+
| 'w3re\'source'        | 
+-----------------------+
1 row in set (0.03 sec)

Example of MySQL QUOTE() function using table

The following statement returns the pub_name and pub_name enclosed with a single quote for those publishers who belong to the ‘USA’.

Code:

SELECT pub_name, QUOTE(pub_name)
FROM publisher 
WHERE country='USA'; 

Sample table: publisher

Output:

mysql> SELECT pub_name, QUOTE(pub_name)
    -> FROM publisher 
    -> WHERE country='USA';
	

 
+--------------------------+----------------------------+
| pub_name                 | QUOTE(pub_name)            |
+--------------------------+----------------------------+
| Jex Max Publication      | 'Jex Max Publication'      | 
| Mountain Publication     | 'Mountain Publication'     | 
| Summer Night Publication | 'Summer Night Publication' | 
+--------------------------+----------------------------+
3 rows in set (0.04 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: POSITION
Next: REGEXP