QUOTE

Returns a string enclosed in single quotes.

Syntax

QUOTE ( str )

Arguments

str

A string.

Return Type

String

Examples

SELECT 'Do';
+----+
| Do |
+----+
| Do |
+----+
SELECT QUOTE('Do');
+-------------+
| QUOTE('Do') |
+-------------+
| 'Do'        |
+-------------+
SELECT QUOTE("Don't");
+----------------+
| QUOTE("Don't") |
+----------------+
| 'Don\'t'       |
+----------------+

In the following example, the result is the string NULL without enclosing quotes:

SELECT QUOTE(NULL);
+-------------+
| QUOTE(NULL) |
+-------------+
| NULL        |
+-------------+

In the following example, QUOTE is used with a dynamic SQL statement to incorporate the value of a string variable.

SET sql_mode = 'PIPES_AS_CONCAT';
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t1(id INT, color TEXT);
CREATE TABLE t2(id INT, color TEXT);
INSERT INTO t1 VALUES (1, 'red'), (2, 'green'), (3, 'blue'), (4, 'green');
INSERT INTO t2 VALUES (1, 'orange'), (2, 'blue'), (3, 'purple'), (4,'orange');
DELIMITER //
CREATE PROCEDURE p(tbl TEXT, color TEXT) AS
DECLARE
str TEXT = 'ECHO SELECT id, color FROM ' || tbl || ' WHERE color = ' ||
QUOTE(color) || ' ORDER BY id';
BEGIN
EXECUTE IMMEDIATE str;
END
//
DELIMITER ;
CALL p('t2','orange');

Output:

+------+--------+
| id | color |
+------+--------+
| 1 | orange |
| 4 | orange |
+------+--------+

Last modified: February 27, 2023

Was this article helpful?