MySQL8 QUOTE() Functions – String Functions

by · Published · Updated

QUOTE() function returns the string surrounded by single quotations and escapes the backslash in it.

The MySQL QUOTE() function quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote (‘), ASCII NUL, and Control+Z preceded by a backslash.

Returns value is the word “NULL” without enclosing single quotation marks if the input is NULL. See example 2. It is useful in saving the values in the database.

MySQL QUOTE() Functions: Syntax

QUOTE ( string );

MySQL QUOTE() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional,Type, Description
string, Required, String , It represents the valid input  (already) escaped string..
[/table]

MySQL QUOTE() Functions: Output

[table caption=”” width=”100%” colwidth=”100%” colalign=”left”]
Return
string surrounded by single quotations and escapes the backslash in it.
Returns value is the word “NULL” without enclosing single quotation marks if the input is NULL.[/table]

MySQL QUOTE() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 5.7
[/table]

QUOTE() Example 1 : Very easy example

it makes MySQL‘s handling of the full string easier by covering the string with single quotes, and by preceding every single quote, backslash, ASCII NUL, and control-Z with a backslash. See the below example:

mysql> SELECT QUOTE('Shouldn\'t!');
+----------------------+
| QUOTE('Shouldn\'t!') |
+----------------------+
| 'Shouldn\'t!'        |
+----------------------+
1 row in set (0.05 sec)

The above example has returned the same string as input. while below example add \(backslash) to single quotation mark(‘).

mysql> SELECT QUOTE('Shouldn''t!');
+----------------------+
| QUOTE('Shouldn''t!') |
+----------------------+
| 'Shouldn\'t!'        |
+----------------------+
1 row in set (0.00 sec)

QUOTE() Example  2 : NULL arguments

See below example

mysql> SELECT QUOTE(NULL);
+-------------+
| QUOTE(NULL) |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)

QUOTE() Example 3 :  Database Example

The QUOTE operator is often used to enclosed with a single quote for all employee’s names. See below example

mysql> select name, QUOTE(name) from tbl_employee;
+------------+--------------+
| name       | QUOTE(name)  |
+------------+--------------+
| Jyoti Rani | 'Jyoti Rani' |
| Polla      | 'Polla'      |
| Arnav      | 'Arnav'      |
| Jeevesh    | 'Jeevesh'    |
| Tom        | 'Tom'        |
+------------+--------------+
5 rows in set (0.03 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : LOCATE(), HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

PHP Related articles : ADDSLASHES(), ADDCSLASHES(), QUOTEMETA(), STRIPSLASHES(), STRIPCSLASHES(), PHP STRING FUNCTIONS().

You may also like...