SQLite § strings

Strings and BLOBs

SQLite stores textual data in the TEXT storage class as a variable-length sequence of UTF-8 bytes (the default), UTF-16BE, or UTF-16LE. Binary data is stored in the BLOB storage class, also variable-length, as an exact sequence of bytes with no encoding interpretation. There is no fixed-length character type — CHAR(20) is accepted as a declared type but the length is not enforced — and there is no separate BYTEA or VARBINARY type; a BLOB is the universal binary container.

This page covers the syntax for string and BLOB literals, the conventional string-manipulation functions, and the encoding considerations that arise when an application interoperates with text stored under non-default encodings.

String literals

A string literal is enclosed in single quotes. A single quote inside the literal is escaped by doubling it. There are no backslash escape sequences and no \n, \t, or \u… forms; control characters are embedded literally or constructed with the char(n) function, which returns a one-character string for the given code point.

SELECT 'Hello';
SELECT 'Ada''s book';
SELECT 'line one' || char(10) || 'line two';

Strings may be concatenated with the || operator (see Operators). There is no + for strings; 'a' + 'b' evaluates to zero because the operands convert numerically.

SELECT 'Hello, ' || 'world!';
SELECT 'page ' || page_no || ' of ' || page_count FROM book;

The double-quote and bracket forms are identifier quotes, not string quotes. A double-quoted token is interpreted as a string literal only as a fallback when no identifier of that name exists — the SQLite-specific behaviour described in Syntax.

BLOB literals

A BLOB literal is written with the X (or x) prefix followed by a single-quoted sequence of an even number of hexadecimal digits. Each pair of hexadecimal digits encodes one byte.

SELECT X'48656C6C6F';      -- the bytes for "Hello" in ASCII
SELECT length(X'cafebabe'); -- 4
SELECT X'00';              -- a one-byte BLOB containing 0x00

A BLOB stored through the C API or a language binding is the recommended form for application binary data; the literal syntax is mostly useful for diagnostic queries and for inserting test fixtures.

Length, substring, and search

The length() function returns the number of characters for a TEXT value (under the database encoding) and the number of bytes for a BLOB. For UTF-8 text containing only ASCII, the two coincide; for text containing non-ASCII characters, length() returns the number of code points.

SELECT length('Ada');           -- 3
SELECT length('café');          -- 4 (one code point per character)
SELECT length(X'01020304');     -- 4 (bytes)

substr(s, start, length) returns the substring of s starting at position start (1-indexed) of the given length. If length is omitted, the substring runs to the end. A negative start counts from the end of the string.

SELECT substr('Ada Lovelace', 1, 3);    -- 'Ada'
SELECT substr('Ada Lovelace', 5);       -- 'Lovelace'
SELECT substr('Ada Lovelace', -3);      -- 'ace'

instr(haystack, needle) returns the 1-based position of the first occurrence of needle within haystack, or 0 if not found. The function is case-sensitive.

SELECT instr('Ada Lovelace', 'Lov');    -- 5
SELECT instr('Ada Lovelace', 'XYZ');    -- 0

replace(s, old, new) replaces every occurrence of old with new.

SELECT replace('Ada Lovelace', 'a', 'A');     -- 'AdA LovelAce'
SELECT replace(phone, '+44', '0') FROM customer;

Trimming and case

trim(s), ltrim(s), rtrim(s) remove leading, trailing, or both leading and trailing whitespace. A second argument specifies the characters to trim instead of whitespace.

SELECT trim('   hello   ');             -- 'hello'
SELECT ltrim('£599.99', '£');           -- '599.99'
SELECT rtrim('599.99 GBP', ' GBP');     -- '599.99'
SELECT trim('***x***', '*');            -- 'x'

upper(s) and lower(s) change the case of ASCII letters; they do not case-fold non-ASCII characters by default. The ICU extension, when loaded, replaces the default implementations with locale-aware versions.

SELECT upper('hello');                  -- 'HELLO'
SELECT lower('HELLO');                  -- 'hello'
SELECT upper('café');                   -- 'CAFé'  (non-ASCII unchanged by default)

Concatenation

The || operator concatenates strings. NULL propagates: if either operand is NULL, the result is NULL. The conventional remedy is coalesce(s, '') or IFNULL(s, '') to substitute an empty string.

SELECT first_name || ' ' || COALESCE(middle_name, '') || ' ' || last_name AS full_name
FROM person;

Formatting: printf / format

printf(fmt, args…) (and its alias format(fmt, args…)) produces a string in the manner of the C printf function. The format specifiers are the conventional %d, %s, %f, %x, %o, with the additional SQLite-specific %q (single-quote-aware), %Q (NULL-or-quoted), and %w (double-quote-aware) for embedding values in generated SQL.

SELECT printf('%5.2f', 3.14159);            -- ' 3.14'
SELECT printf('%-10s | %d', name, age) FROM person;
SELECT printf('%q', 'Ada''s book');         -- 'Ada''s book'  (SQL-safe)

format is the preferred name in 3.38+; it is identical to printf and is offered as a more readable spelling.

Padding and repetition

char(n1, n2, …) returns a string assembled from the given Unicode code points. unicode(s) returns the code point of the first character of s.

SELECT char(72, 105);             -- 'Hi'
SELECT unicode('A');              -- 65

There is no built-in lpad or rpad or repeat. The conventional substitutes are printf for padding and replace(substr('00000000', 1, n), '0', '0') patterns for repetition; modern SQL would express these as recursive CTEs. The bindings for major languages typically expose a string-padding helper that is more idiomatic than the SQL-level workaround.

Patterns: LIKE, GLOB, REGEXP

LIKE and GLOB are the conventional string-pattern operators in SQLite SQL; they are described in Operators. The summary:

LIKE 'A%' matches strings starting with A; the _ metacharacter matches a single character; matching is ASCII-case-insensitive by default.
GLOB 'A*' matches in the manner of Unix glob expansion; the ? metacharacter matches a single character; matching is case-sensitive.
REGEXP calls a user-defined function, typically registered by the language binding. The standard sqlite3 shell does not bundle a regex implementation.

The full-text-search module FTS5 implements a richer query language (column qualifiers, prefix matching, proximity, boolean operators) over text-heavy datasets and is the preferred mechanism for substantial text search; see Full-text search.

Encoding

The encoding of text in a database is fixed at creation time and is one of UTF-8, UTF-16BE, or UTF-16LE. The default is UTF-8 and is the recommended choice for new databases. The encoding is set with PRAGMA encoding:

PRAGMA encoding = 'UTF-8';        -- once, before any tables exist
PRAGMA encoding;                  -- inspect: 'UTF-8' / 'UTF-16le' / 'UTF-16be'

Once tables exist, the encoding cannot be changed. Mixing encodings between attached databases is not permitted: an ATTACH of a UTF-16 database to a UTF-8 connection fails.

Collations

A collation defines the comparison order for text values. SQLite ships three built-in collations:

Collation	Meaning
`BINARY`	Byte-by-byte (default)
`NOCASE`	ASCII-case-insensitive
`RTRIM`	Trailing whitespace ignored

Collations are applied per-column in CREATE TABLE, per-index in CREATE INDEX, or per-comparison with COLLATE:

CREATE TABLE user (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL COLLATE NOCASE
);

SELECT * FROM user WHERE email = 'ada@example.com' COLLATE NOCASE;

A UNIQUE index on email declared with NOCASE collation prevents two rows differing only in case. Custom collations can be registered through the C API (sqlite3_create_collation); the conventional use is locale-aware sorting through the ICU library, which is shipped as an optional extension.

BLOBs

BLOBs accept and preserve arbitrary byte sequences. length() returns the byte count; substr() returns a sub-range; || concatenates BLOBs as it does strings. The dedicated incremental I/O API (sqlite3_blob_open, sqlite3_blob_read, sqlite3_blob_write) permits streaming access to a stored BLOB without loading it entirely into memory; this is the recommended interface for stored images, attached files, and other large binary objects.

The historical recommendation against storing large BLOBs in SQLite — that the engine is optimised for small structured records — is no longer accurate. Benchmarks at sqlite.org/fasterthanfs.html show that, for files smaller than approximately one hundred kilobytes, SQLite reads them faster than the host file system, primarily because the database avoids per-file open and stat overhead. The contemporary advice is that BLOBs are an appropriate storage choice for small to medium binary objects (icons, thumbnails, attachments under a few megabytes) and that very large BLOBs (multi-megabyte video clips) are still typically better as files on disk with paths in the database.

Quoting in generated SQL

The printf family’s %q, %Q, and %w specifiers exist for the rare situation in which SQL must be assembled programmatically. %q doubles embedded single quotes, producing a literal that is safe to embed in single quotes; %Q is the same as %q but renders NULL as the literal NULL rather than '(null)'; %w doubles embedded double quotes for use in identifiers.

SELECT printf('INSERT INTO log VALUES (%Q, %d)', message, level)
FROM event;

The recommended practice is not to assemble SQL programmatically: the parameterised forms described in Syntax are correct, faster (the prepared statement is cached), and immune to SQL injection. The %q family is for the unusual case in which SQL itself is being generated as data.