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
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P002 | BPP Publication | Mumbai | India | New Delhi | 10 | 1985-10-01 | | P003 | New Harrold Publication | Adelaide | Australia | Sydney | 6 | 1975-09-05 | | P004 | Ultra Press Inc. | London | UK | London | 8 | 1948-07-10 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | | P007 | Pieterson Grp. of Publishers | Cambridge | UK | London | 6 | 1950-07-15 | | P008 | Novel Publisher Ltd. | New Delhi | India | Bangalore | 10 | 2000-01-01 | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+
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: