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:
| Name | Description |
|---|---|
| str | A string. |
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: