SQLite | Data Type | Escaping Strings

String values stored in SQLite tables are enclosed in single quotes ('). A single quote within the string must be escaped. This article explains how to escape strings.

Escaping a String

Write a string value in SQLite by enclosing it in single quotes (') as follows.

'string'

Text enclosed in double quotes (") is an identifier. An identifier may appear to be treated as a string when it is used where a string is expected, but strings must be enclosed in single quotes.

Because single quotes delimit strings, a single quote within a string must be escaped.

Create the following table for this example.

create table test (val text);

If a test table with the same name already exists, drop it before creating the new one.

sqlite> drop table test;
sqlite> create table test (val text);
sqlite>

Suppose you want to store the following string, which contains a single quote.

I'm a student.

Executing the following statement does not fail immediately, but the SQL statement remains incomplete and waits for more input. SQLite cannot determine where the string begins and ends.

insert into test values('I'm a student.');

sqlite> insert into test values('I'm a student.');
   ...> 
   ...> ';
Error: near "m": syntax error
sqlite> 

Escape a single quote inside a string by writing another single quote immediately before it.

insert into test values('I''m a student.');

sqlite> 
sqlite> insert into test values('I''m a student.');
sqlite> 

The value is now stored successfully. Query the table to verify it.

select * from test;

sqlite> select * from test;
I'm a student.

A string containing a single quote will not be stored correctly unless the quote is escaped. Always escape single quotes embedded in string values.