QUOTE

Syntax

SQL

QUOTE ( str )

Arguments

str

A string.

Return Type

String

Examples

SQL

SELECT 'Do';

+----+
| Do |
+----+
| Do |
+----+

SQL

SELECT QUOTE('Do');

+-------------+
| QUOTE('Do') |
+-------------+
| 'Do'        |
+-------------+

SQL

SELECT QUOTE("Don't");

+----------------+
| QUOTE("Don't") |
+----------------+
| 'Don\'t'       |
+----------------+

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

SQL

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.

SQL

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 |
+------+--------+