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.
SELECT QUOTE('it''s'); -- 'it''s'
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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) |
-- 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
NULLall look alike in a result grid, butQUOTE()tells them apart:'',' 'andNULL. - Numbers keep their form.
QUOTE('42')is'42'(text) butQUOTE(42)is42. - Real numbers print with enough digits to read back exactly, so
QUOTE(0.1)is0.1. - Concatenation propagates NULL, so when generating statements use
QUOTE(x)rather than'''' || x || '''': the latter producesNULLfor missing data.