Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL QUOTE() function

Reference for SQL QUOTE(): turn a value into text you can paste into a query as a literal, how strings, NULL and blobs are written, and the vendor equivalents.

QUOTE(x) returns the SQL literal for a value: the text you would have to type in a statement to produce that value. Strings come back wrapped in single quotes with embedded quotes doubled, numbers appear as they are, NULL becomes the four letters NULL, and blobs become X'...' literals.

It is handy for debugging ("what exactly is in this cell?"), for generating INSERT statements from existing rows and for seeing invisible differences such as trailing spaces.

SQL
SELECT QUOTE('it''s');   -- 'it''s'

Try it

SQL How each kind of value is written

Press Run (or Ctrl + Enter) to see the result here.

SQL Generating INSERT statements ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
QUOTE(x)
Name Description
x Any value: text, number, blob or NULL.

Returns: text containing a valid SQL literal for x. It never returns SQL NULL; for a NULL argument the result is the text NULL.

Dialect differences

Database Equivalent
SQLite QUOTE(x) for any value; printf('%Q', x) is similar
PostgreSQL quote_literal(text) for values, quote_nullable(x) (gives NULL for null), quote_ident(text) for identifiers, and format('%L', x) / format('%I', x)
MySQL QUOTE(str) escapes with a backslash ('it\'s') and returns the word NULL without quotes for NULL
SQL Server QUOTENAME(name) quotes identifiers with brackets; for values, double the quote yourself with REPLACE(s, '''', '''''')
Oracle DBMS_ASSERT.ENQUOTE_LITERAL(s) and DBMS_ASSERT.ENQUOTE_NAME(s)
SQL
-- PostgreSQL
SELECT quote_literal('it''s');        -- 'it''s'
SELECT quote_ident('Order Items');    -- "Order Items"

-- SQL Server (identifiers)
SELECT QUOTENAME('Order Items');      -- [Order Items]

Usage notes

  • Not a security tool. Never build queries from user input by quoting strings yourself. Use parameterized statements (see SQL injection); quoting is for debugging and for generating scripts from trusted data.
  • Identifiers are different. QUOTE() is for values. Table and column names are quoted with double quotes (standard), backticks (MySQL) or brackets (SQL Server).
  • Seeing the invisible. Trailing spaces, empty strings and NULL all look alike in a result grid, but QUOTE() tells them apart: '', ' ' and NULL.
  • Numbers keep their form. QUOTE('42') is '42' (text) but QUOTE(42) is 42.
  • Real numbers print with enough digits to read back exactly, so QUOTE(0.1) is 0.1.
  • Concatenation propagates NULL, so when generating statements use QUOTE(x) rather than '''' || x || '''': the latter produces NULL for missing data.