The SQLite quote() function allows you to escape a string so that it’s suitable for inclusion in an SQL statement.

Strings are surrounded by single-quotes with escapes on interior quotes.

BLOBs are encoded as hexadecimal literals.

Note that strings with embedded NUL characters cannot be represented as string literals in SQL. If you include strings with embedded NUL characters, the returned string literal is truncated prior to the first NUL.

Example

Here’s a simple example of the quote() function to demonstrate how it works.

SELECT 
  'Money',
  quote('Money');

Result:

'Money'     quote('Money')        
----------  ----------------------
Money       'Money'               

Notice that the second column is returned with single quotes.

Generating an SQL Statement

The quote() function could come in handy if you ever need to generate an SQL INSERT statement from the contents of an existing database table.

For example, I have the following table called Products:

ProductId   ProductName     Price     
----------  --------------  ----------
1           Homer's Widget  10.0      
2           Peter's Widget  20.75     
3           Bob's Widget    15.0      

Each product name contains a single quote character. If I wanted to generate an INSERT statement with these values, then I’d need to escape the single quote characters.

Here’s an example of generating INSERT statements from this table, and using quote() to escape the single quote characters.

SELECT 
  'INSERT INTO Products2 (ProductName, Price) 
   VALUES (' || quote(ProductName) || ', ' || Price || ');'
FROM Products;

Result:

INSERT INTO Products2 (ProductName, Price) 
   VALUES ('Homer''s Widget', 10.0);                        
INSERT INTO Products2 (ProductName, Price) 
   VALUES ('Peter''s Widget', 20.75);                       
INSERT INTO Products2 (ProductName, Price) 
   VALUES ('Bob''s Widget', 15.0);