🔤

SQLite String Functions

SQLite

Complete reference for SQLite string functions covering lower, upper, trim, substr, instr, concat, format, printf, hex, unhex, and unicode. Every function includes syntax, real SQL examples, and differences from PostgreSQL string functions. Updated for SQLite 3.52.

20 functions

Jump to function

concat concat_ws format hex instr length like lower ltrim octet_length printf replace rtrim soundex substr substring trim unhex unicode upper

What are SQLite String Functions?

SQLite string functions are built-in functions for manipulating text data in SQLite queries. Unlike PostgreSQL, SQLite uses dynamic typing so string functions will attempt to coerce non-text arguments. Key SQLite string functions include substr() for substring extraction, instr() for finding a substring position, printf()/format() for formatted strings, and trim()/ltrim()/rtrim() for whitespace removal. Note that SQLite string concatenation uses || rather than concat() in older versions.

concat

SQLite 3.44.0+→ TEXT

Returns a string which is the concatenation of the string representation of all of its non-NULL arguments; if all arguments are NULL, returns an empty string.

Signature

concat(X, ...)

Parameters

Parameter	Type	Description
X, ...	ANY	One or more values to concatenate; NULL values are silently skipped.

Examples

Basic three-part concatenation

sql

SELECT concat('Hello', ' ', 'World');

→'Hello World'

NULL arguments are skipped, not propagated

sql

SELECT concat('foo', NULL, 'bar');

→'foobar'

Numeric arguments are coerced to text

sql

SELECT concat(42, '-', 99);

→'42-99'

All-NULL arguments return an empty string, not NULL

sql

SELECT concat(NULL, NULL);

→''

Practical: building a full name from table columns

sql

SELECT concat(first_name, ' ', last_name) AS full_name FROM users WHERE id = 1;

→'Jane Doe'

⚠Anti-Pattern— Silent NULL drops masking missing data

Because concat() silently skips NULLs, concat(first_name, ' ', last_name) returns 'Jane' when last_name is NULL instead of signalling the gap. This makes it impossible to distinguish a stored empty string from a genuinely absent value. If propagating NULL is the desired behaviour — so the caller knows data is incomplete — use the || operator instead.

✓ Instead: Use the || operator (first_name || ' ' || last_name) when a NULL component should make the entire result NULL and surface the missing data to the caller.

In SQLite the || operator propagates NULL — 'hello' || NULL yields NULL. concat() silently drops NULL arguments instead, making it safer when columns may be empty. For a separator between non-null parts, prefer concat_ws().

◆SQLite Note

Added in SQLite 3.44.0 (2023-11-01). Unlike PostgreSQL's concat(), which also skips NULLs, SQLite's concat() returns an empty string (not NULL) when every argument is NULL. The || operator in SQLite is an alternative but propagates NULL on any null operand.

concat_ws

SQLite 3.44.0+→ TEXT

Returns a string that is the concatenation of all non-NULL arguments after the first, using the first argument as a separator between each pair; returns NULL if the separator is NULL.

Signature

concat_ws(SEP, X, ...)

Parameters

Parameter	Type	Description
SEP	TEXT	The separator string placed between each non-NULL value; if NULL the entire result is NULL.
X, ...	ANY	One or more values to join; NULL values are skipped (the separator is not inserted for them).

Examples

Comma-separated list

sql

SELECT concat_ws(', ', 'Alice', 'Bob', 'Carol');

→'Alice, Bob, Carol'

Building a date string from parts

sql

SELECT concat_ws('-', '2024', '01', '15');

→'2024-01-15'

NULL values are skipped; no double separator

sql

SELECT concat_ws(', ', 'Alice', NULL, 'Carol');

→'Alice, Carol'

NULL separator makes the whole result NULL

sql

SELECT concat_ws(NULL, 'a', 'b');

→NULL

Practical: assembles full name, gracefully omitting absent middle names

sql

SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name FROM employees WHERE dept = 'eng';

→'Ada Byron Lovelace'

⚠Anti-Pattern— Passing NULL as the separator silently nullifies the result

Unlike the value arguments — where NULLs are simply skipped — a NULL separator causes concat_ws() to return NULL for the entire expression. Developers who store the separator in a column or variable and forget to guard against NULL end up with a NULL result and no warning. This is especially surprising because the value NULLs are handled gracefully.

✓ Instead: Always ensure the separator argument is a non-NULL literal or use COALESCE(sep_col, ',') to provide a fallback before passing it to concat_ws().

concat_ws() is the cleanest way to build comma-separated or slash-separated strings from nullable columns. Because it only inserts the separator between present values, you never get leading, trailing, or doubled separators — a common bug when using || manually.

◆SQLite Note

Added in SQLite 3.44.0 (2023-11-01), matching the PostgreSQL and MySQL function of the same name. Behaviour is identical to PostgreSQL: NULL separator returns NULL; NULL value arguments are skipped entirely.

format

SQLite 3.38.0+→ TEXT

Returns a string constructed from a printf-style format string, substituting subsequent arguments for format specifiers such as %s, %d, %f, and %x.

Signature

format(FORMAT, ...)

Parameters

Parameter	Type	Description
FORMAT	TEXT	A printf-style format string; if NULL the result is NULL.
...	ANY	Zero or more values substituted for format specifiers in order; missing arguments are treated as NULL (0 for numeric, '' for %s).

Examples

Basic string substitution

sql

SELECT format('Hello, %s!', 'world');

→'Hello, world!'

Zero-padded integer to fixed width

sql

SELECT format('%05d', 42);

→'00042'

Float rounded to two decimal places

sql

SELECT format('%.2f', 3.14159);

→'3.14'

Multiple substitutions from table columns

sql

SELECT format('%s has %d items', category, count) FROM inventory WHERE id = 1;

→'Widgets has 150 items'

Using %w (SQLite-specific) to safely quote an identifier

sql

SELECT format('UPDATE %w SET active=0 WHERE id=%d', tbl, row_id) FROM audit_queue;

→'UPDATE "users" SET active=0 WHERE id=7'

⚠Anti-Pattern— Using %s for numeric values causes implicit TEXT conversion

Formatting a numeric column with %s coerces the value to its text representation before substitution, which can introduce unexpected scientific notation (e.g., 1e6 instead of 1000000) for large numbers and strips trailing zeros from floats. The result is a TEXT string that may not sort or compare numerically as expected downstream.

✓ Instead: Use the appropriate numeric specifier (%d for integers, %f or %.Nf for floats) so the formatting is explicit and predictable rather than relying on SQLite's implicit text coercion.

SQLite's format() supports a non-standard %w specifier that doubles any internal double-quotes and wraps the value in double quotes, making it safe to embed dynamic table or column names. Use %q to produce a single-quoted SQL string literal with internal quotes properly escaped.

◆SQLite Note

format() was added in SQLite 3.38.0 (2022-02-22) as a renamed alias of printf(), which remains available for backwards compatibility. PostgreSQL's format() uses %I and %L specifiers for identifiers and literals; SQLite uses %w and %q instead. The %n specifier is silently ignored in SQLite.

hex

SQLite 3.x+→ TEXT

Interprets its argument as a BLOB and returns an uppercase hexadecimal string representing the bytes of that blob.

Signature

hex(X)

Parameters

Parameter	Type	Description
X	BLOB \| TEXT \| INTEGER \| REAL	The value to encode; integers and reals are first converted to their UTF-8 text representation before hex-encoding.

Examples

UTF-8 bytes of the string encoded as uppercase hex

sql

SELECT hex('ABC');

→'414243'

BLOB literal passed through unchanged (already binary)

sql

SELECT hex(x'DEADBEEF');

→'DEADBEEF'

Integer 255 becomes text '255', then hex-encoded

sql

SELECT hex(255);

→'323535'

Common pattern: generate a UUID-like random hex identifier

sql

SELECT lower(hex(randomblob(16)));

→'a3f2...c1d0'

Practical: display a BLOB column as readable hex

sql

SELECT id, hex(signature) AS hex_sig FROM documents WHERE id = 42;

→'4F2A...'

⚠Anti-Pattern— Calling hex() on TEXT to get a display-safe encoding

Developers sometimes call hex() on a TEXT column intending to produce a human-readable or URL-safe representation, but hex() encodes the raw UTF-8 bytes of the string — so hex('Hello') returns '48656C6C6F', not something visually meaningful. The output is always uppercase, and multi-byte characters produce multi-byte hex sequences that are hard to interpret without decoding.

✓ Instead: If you need a URL-safe or display encoding use application-level base64 or percent-encoding; reserve hex() for genuine BLOB or binary data where a byte-level hex dump is the actual goal.

hex(12345) returns '3132333435' — the hex of the ASCII digits '12345', not '0000000000003039'. If you want the binary representation of a number, use a BLOB cast: hex(CAST(12345 AS BLOB)) is not supported directly; store the number as a BLOB via application code instead.

◆SQLite Note

Unlike PostgreSQL's encode(val, 'hex') or the to_hex() function, SQLite's hex() always operates on the UTF-8 or BLOB bytes of its argument. Numeric arguments are coerced to their text representation before encoding, which surprises most PostgreSQL users. Output is always uppercase; wrap with lower() if lowercase is needed.

instr

SQLite 3.7.15+→ INTEGER

Returns the 1-based character position of the first occurrence of string Y within string X, or 0 if Y is not found.

Signature

instr(X, Y)

Parameters

Parameter	Type	Description
X	TEXT \| BLOB	The string (or BLOB) to search within.
Y	TEXT \| BLOB	The substring (or BLOB pattern) to search for.

Examples

Returns 1-based position of the found substring

sql

SELECT instr('hello world', 'world');

→7

Returns 0 (not NULL) when the substring is not found

sql

SELECT instr('hello world', 'xyz');

→0

Returns position of the FIRST occurrence only

sql

SELECT instr('abcabc', 'b');

→2

Returns NULL when either argument is NULL

sql

SELECT instr(NULL, 'x');

→NULL

Practical: flag log rows that contain the word ERROR

sql

SELECT id, instr(body, 'ERROR') > 0 AS has_error FROM log_entries WHERE ts > '2024-01-01';

→1 | 1

⚠Anti-Pattern— Using instr() as a bare boolean in a WHERE clause

Writing WHERE instr(body, 'ERROR') instead of WHERE instr(body, 'ERROR') > 0 looks like a boolean check but is subtly wrong: instr() returns 0 when the substring is absent, and in SQLite 0 is falsy, so it accidentally works — until the substring appears at position 1, which is also truthy, and so the logic is accidentally correct. The real bug surfaces if someone reads the code and assumes 0 means not-found and NULL means an error, leading to misinterpretation.

✓ Instead: Always write instr(col, needle) > 0 explicitly to make the intent unambiguous and avoid confusion with the NULL-returning behaviour when either argument is NULL.

Unlike many functions, instr() returns 0 when the needle is absent — so use WHERE instr(col, 'keyword') > 0 to filter matches, not IS NOT NULL. This also means you can safely use it in arithmetic without a COALESCE wrapper.

◆SQLite Note

SQLite's instr() returns 0 when Y is not found, whereas PostgreSQL's equivalent strpos() returns 0 as well — so this behaviour is consistent. However, if either argument is NULL, SQLite returns NULL (unlike strpos which would return NULL too). The key SQLite quirk is that instr() takes only 2 arguments; it has no start-position parameter. To find the second occurrence, use substr() to skip past the first match and call instr() again.

length

SQLite 3.x+→ INTEGER

Returns the number of Unicode code points in string X (character length, not byte length), or the number of bytes if X is a BLOB.

Signature

length(X)

Parameters

Parameter	Type	Description
X	TEXT \| BLOB \| INTEGER \| REAL	The value whose length is measured; numeric values are first converted to their text representation.

Examples

Basic ASCII string length in characters

sql

SELECT length('hello');

→5

Multi-byte UTF-8 character counts as 1 code point

sql

SELECT length('café');

→4

For BLOBs, returns byte count (not character count)

sql

SELECT length(x'DEADBEEF');

→4

NULL input yields NULL output

sql

SELECT length(NULL);

→NULL

Practical: find articles exceeding a character budget

sql

SELECT id, title, length(body) AS char_count FROM articles WHERE length(body) > 10000;

→42 | 'Long Article' | 15432

⚠Anti-Pattern— Using length() to measure the byte size of a BLOB

length() does return bytes for BLOB arguments, but when you cast or coerce a BLOB to TEXT before calling length(), it counts characters instead of bytes — silently giving the wrong answer for multi-byte content. Developers who rely on length() for size checks on binary payloads risk truncation or buffer overflows when the data contains multi-byte sequences.

✓ Instead: Call octet_length() explicitly for byte-size checks on both TEXT and BLOB values, reserving length() only for character-count operations on TEXT.

For ASCII-only data these are the same, but for any string containing multi-byte UTF-8 characters (accents, CJK, emoji) length() returns the code-point count while octet_length() returns the byte count. Always use octet_length() when computing storage sizes or interoperating with byte-oriented APIs.

◆SQLite Note

SQLite's length() counts Unicode code points for TEXT, which matches PostgreSQL's char_length() / character_length(). PostgreSQL also has length() but it counts bytes for bytea. SQLite has no bytea type — BLOBs are used instead, and length() on a BLOB returns bytes. Use octet_length() for the byte length of a TEXT value in SQLite.

like

SQLite 3.x+→ INTEGER

Tests whether string Y matches the LIKE pattern X, returning 1 (true) or 0 (false); the optional third argument Z specifies an escape character.

Signatures

like(X, Y)

like(X, Y, Z)

Parameters

Parameter	Type	Description
X	TEXT	The LIKE pattern; % matches any sequence of zero or more characters, _ matches exactly one character.
Y	TEXT	The string to test against the pattern.
Z	TEXT	Optional single-character escape prefix; a literal % or _ in the pattern must be preceded by this character.

Examples

% wildcard matches any sequence of characters

sql

SELECT like('%world%', 'hello world');

→1

_ wildcard matches exactly one character

sql

SELECT like('h_llo', 'hello');

→1

LIKE is case-insensitive for ASCII letters by default

sql

SELECT like('H%', 'hello');

→1

Escape character ! lets you match a literal %

sql

SELECT like('%50!%%', 'sale 50%', '!');

→1

Practical: case-insensitive substring search in a table

sql

SELECT name FROM products WHERE name LIKE '%widget%';

→'Super Widget'

⚠Anti-Pattern— Leading wildcard patterns and unescaped user input

Using LIKE '%keyword%' with a leading wildcard forces a full table scan regardless of indexes, which degrades linearly with table size. A separate but equally dangerous mistake is interpolating user-supplied search terms directly into the pattern without escaping — a user who searches for '100%' or 'price_drop' inadvertently uses % and _ as wildcards, producing incorrect matches.

✓ Instead: For substring search at scale use FTS5; for ad-hoc patterns, bind the search term as a parameter and escape any literal % or _ in the value using the ESCAPE clause before concatenating the wildcards.

SQLite can use a B-tree index to accelerate 'prefix%' patterns (e.g., WHERE name LIKE 'Acme%') because the prefix narrows the scan range. A leading wildcard like '%widget' forces a full table scan. For full-text or substring search at scale, consider the FTS5 extension.

◆SQLite Note

SQLite's LIKE operator (and the like() function) is case-insensitive for ASCII letters (A–Z) by default, matching PostgreSQL's ILIKE rather than LIKE. Unicode letters outside ASCII are case-sensitive unless the ICU extension is loaded. PostgreSQL's LIKE is case-sensitive; use ILIKE for case-insensitive matching. The ESCAPE clause syntax is the same in both databases.

lower

SQLite 3.x+→ TEXT

Returns a copy of string X with all ASCII uppercase letters converted to their lowercase equivalents.

Signature

lower(X)

Parameters

Parameter	Type	Description
X	TEXT	The string to convert to lowercase.

Examples

Basic ASCII lowercase conversion

sql

SELECT lower('Hello World');

→'hello world'

Mixed alphanumeric — digits unchanged

sql

SELECT lower('SQLite 3.44');

→'sqlite 3.44'

Non-ASCII character É is NOT lowercased without the ICU extension

sql

SELECT lower('CAFÉ');

→'cafÉ'

Common idiom: generate a lowercase hex identifier

sql

SELECT lower(hex(randomblob(8)));

→'a1b2c3d4e5f60718'

Practical: case-insensitive email lookup via normalisation

sql

SELECT * FROM users WHERE lower(email) = lower('User@Example.COM');

→-- matching row

⚠Anti-Pattern— Using lower(col) = lower(?) in a WHERE clause without a functional index

Wrapping a column in lower() inside a WHERE clause prevents SQLite from using a standard B-tree index on that column, causing a full table scan on every query. This is an easy performance trap on large tables because the query works correctly but silently scales poorly as row count grows.

✓ Instead: Create a functional index — CREATE INDEX idx_lower_col ON tbl(lower(col)); — and always query with WHERE lower(col) = lower(?), so the index is used and the scan is avoided.

Create a functional index — CREATE INDEX idx_email_lower ON users(lower(email)); — then query with WHERE lower(email) = lower(?). This avoids a full table scan and correctly handles mixed-case data without changing stored values.

◆SQLite Note

SQLite's built-in lower() only folds ASCII letters (A–Z). Characters outside the ASCII range — accented letters, Greek, Cyrillic, etc. — are returned unchanged. PostgreSQL's lower() handles full Unicode case folding natively. To get Unicode-aware lowercasing in SQLite, compile with or load the ICU extension.

ltrim

SQLite 3.x+→ TEXT

Returns X with leading spaces removed; if Y is supplied, removes any characters found in Y from the left side of X instead.

Signatures

ltrim(X)

ltrim(X, Y)

Parameters

Parameter	Type	Description
X	TEXT	The source string to trim.
Y	TEXT	Optional set of characters to strip; every character in Y is removed from the left end of X until a character not in Y is encountered.

Examples

Remove leading spaces (default behaviour)

sql

SELECT ltrim('   hello');

→'hello'

Only the left side is trimmed

sql

SELECT ltrim('   hello   ');

→'hello '

Remove a specific character from the left

sql

SELECT ltrim('###hello###', '#');

→'hello###'

Y is a set of chars, not a prefix string — order does not matter

sql

SELECT ltrim('xyxhello', 'xy');

→'hello'

Practical: strip leading zeros from serial number strings

sql

SELECT ltrim(raw_code, '0') AS trimmed FROM serial_numbers WHERE product_id = 7;

→'1234'

⚠Anti-Pattern— Treating the Y argument as a literal prefix string

ltrim(col, 'abc') is often written with the intent of removing the exact leading substring 'abc', but the Y argument is a character set — every individual character a, b, and c is stripped from the left in any order and combination. So ltrim('cabbage', 'abc') returns 'bage', not 'bage' from 'cabbage' minus 'cab', because 'c', 'a', 'b' are all in the set and get peeled off one by one.

✓ Instead: To strip a specific literal prefix, use CASE WHEN substr(col, 1, length(prefix)) = prefix THEN substr(col, length(prefix) + 1) ELSE col END rather than ltrim().

ltrim('abcHello', 'abc') removes every leading a, b, or c in any order — it does not strip the literal prefix 'abc'. If you need to remove a specific prefix string, use CASE WHEN substr(col,1,3)='abc' THEN substr(col,4) ELSE col END or replace() instead.

◆SQLite Note

SQLite's two-argument ltrim(X, Y) removes any characters in the set Y from the left of X — identical to PostgreSQL's ltrim(string, characters) behaviour. PostgreSQL also has a one-argument ltrim(text) that strips spaces, same as SQLite. Both dialects treat Y as a character set rather than a literal substring.

octet_length

SQLite 3.43.0+→ INTEGER

Returns the number of bytes in the encoding of string X (or the byte length of a BLOB), as opposed to the number of characters.

Signature

octet_length(X)

Parameters

Parameter	Type	Description
X	TEXT \| BLOB \| INTEGER \| REAL	The value whose byte length is measured; numeric values are counted in their text representation.

Examples

ASCII string: byte length equals character length

sql

SELECT octet_length('hello');

→5

é is 2 bytes in UTF-8, so 4 chars = 5 bytes

sql

SELECT octet_length('café');

→5

BLOB: byte length (same as length() for BLOBs)

sql

SELECT octet_length(x'DEADBEEF');

→4

NULL input returns NULL

sql

SELECT octet_length(NULL);

→NULL

Practical: find rows whose UTF-8 byte size exceeds a storage limit

sql

SELECT id, length(bio) AS chars, octet_length(bio) AS bytes FROM profiles WHERE octet_length(bio) > 65535;

→12 | 32000 | 68000

⚠Anti-Pattern— Using length() where byte count matters for multi-byte strings

Developers accustomed to ASCII-only data often substitute length() for octet_length() when enforcing byte-based limits such as HTTP header sizes, fixed-width file fields, or external API constraints. For strings containing accented characters, CJK glyphs, or emoji, length() underreports the true byte count, allowing oversized values to slip through the guard.

✓ Instead: Use octet_length() any time the constraint is expressed in bytes rather than characters, and document the distinction explicitly in schema comments.

When enforcing byte-based constraints — network packet limits, database column byte limits, or VARCHAR(n) equivalents — always use octet_length() rather than length(). A string with 100 emoji characters can occupy 400 bytes, but length() would report only 100.

◆SQLite Note

octet_length() was added to match the SQL standard and PostgreSQL's function of the same name. In PostgreSQL, octet_length() on a text value counts UTF-8 bytes; it is equivalent to length(val::bytea). In SQLite the result depends on the database encoding: UTF-8 databases return UTF-8 byte counts, UTF-16 databases return UTF-16 byte counts.

printf

SQLite 3.8.3+ (printf); 3.38.0+ (format alias)→ TEXT

An alias for the format() function; returns a string built from a printf-style format string with subsequent arguments substituted for format specifiers.

Signature

printf(FORMAT, ...)

Parameters

Parameter	Type	Description
FORMAT	TEXT	A printf-style format string; if NULL the result is NULL.
...	ANY	Zero or more values substituted for format specifiers in order.

Examples

Basic string substitution — identical to format()

sql

SELECT printf('Hello, %s!', 'SQLite');

→'Hello, SQLite!'

Zero-padded float with two decimal places

sql

SELECT printf('%010.2f', 1234.5);

→'001234.50'

Integer formatted as lowercase hex

sql

SELECT printf('%x', 255);

→'ff'

SQLite %q specifier escapes single quotes for safe SQL embedding

sql

SELECT printf('%q', 'O''Brien');

→'O''Brien'

Practical: generate a human-readable progress string from columns

sql

SELECT printf('Row %d of %d', row_num, total) FROM progress WHERE job_id = 5;

→'Row 42 of 100'

⚠Anti-Pattern— Using %s for numeric arguments causing implicit TEXT conversion

Passing a numeric column to printf() with %s forces SQLite to convert the number to its text representation first, which can produce scientific notation for very large or very small values (e.g., 1.5e-10 instead of 0.00000000015) and silently loses precision. The resulting string is TEXT, not a number, which breaks any downstream numeric comparison or arithmetic.

✓ Instead: Match the format specifier to the actual data type: use %d for integers and %f or %.Nf for floating-point values to get predictable, precision-controlled output.

SQLite originally named this function printf() and later renamed it to format() for cross-database compatibility. Both names work identically. New code should use format() so that queries are easier to port to PostgreSQL, which has its own format() function (with different specifiers).

◆SQLite Note

printf() is exactly an alias for format(). format() was added in SQLite 3.38.0 (2022-02-22) as the preferred name; printf() dates back to SQLite 3.8.3 (2014-02-03). PostgreSQL has no printf() function; its equivalent is format(), but PostgreSQL's format() uses %s, %I (identifier), and %L (literal) specifiers rather than SQLite's %s, %w, %q.

replace

SQLite 3.x+→ TEXT

Returns a copy of string X in which every occurrence of string Y has been replaced by string Z using binary (case-sensitive) comparison.

Signature

replace(X, Y, Z)

Parameters

Parameter	Type	Description
X	TEXT	The source string in which replacements are made.
Y	TEXT	The substring to search for; if empty, X is returned unchanged.
Z	TEXT	The replacement string; if not a string it is cast to UTF-8 text before use.

Examples

Basic substring replacement

sql

SELECT replace('hello world', 'world', 'SQLite');

→'hello SQLite'

All occurrences are replaced, not just the first

sql

SELECT replace('aabbcc', 'b', 'X');

→'aaXXcc'

Empty search string Y returns X unchanged

sql

SELECT replace('hello', '', 'X');

→'hello'

Replace spaces with underscores (slug generation)

sql

SELECT replace('foo bar baz', ' ', '_');

→'foo_bar_baz'

Practical: convert HTML line breaks to newline characters

sql

SELECT replace(description, '<br>', char(10)) FROM articles WHERE id = 3;

→'Line one\nLine two'

⚠Anti-Pattern— Chaining many replace() calls for multi-pattern substitution

Nesting five or more replace() calls to sanitise a string is hard to read, error-prone to maintain, and scans the entire string once per call — so performance degrades linearly with the number of patterns. It is also easy to introduce ordering bugs where an earlier replacement creates a new match for a later pattern.

✓ Instead: For many simultaneous substitutions, handle the transformation in application code using a single-pass approach (e.g., a translation map), or use the regexp_replace() extension if available to cover multiple patterns in fewer passes.

SQLite has no translate() function (unlike PostgreSQL), but you can nest replace() calls: replace(replace(replace(col, 'a', ''), 'e', ''), 'i', '') to strip specific characters. For complex multi-pattern replacements, consider using the regexp_replace() extension if available.

◆SQLite Note

SQLite's replace() uses the BINARY collating sequence — comparisons are always byte-for-byte case-sensitive. PostgreSQL's replace() is also case-sensitive. Neither database offers a built-in case-insensitive replace; the common workaround in SQLite is to combine lower()/upper() with replace() and post-process the result. PostgreSQL users should use regexp_replace() with the 'i' flag for case-insensitive replacement.

rtrim

SQLite 3.x+→ TEXT

Returns X with trailing spaces removed; if Y is supplied, removes any characters found in Y from the right side of X instead.

Signatures

rtrim(X)

rtrim(X, Y)

Parameters

Parameter	Type	Description
X	TEXT	The source string to trim.
Y	TEXT	Optional set of characters to strip from the right end of X.

Examples

Remove trailing spaces (default behaviour)

sql

SELECT rtrim('hello   ');

→'hello'

Only the right side is trimmed

sql

SELECT rtrim('   hello   ');

→' hello'

Remove a specific trailing character

sql

SELECT rtrim('hello###', '#');

→'hello'

Y is a character set — any trailing char in the set is removed

sql

SELECT rtrim('hello world', 'dlrow ');

→'hello'

Practical: normalise directory paths by removing trailing slashes

sql

SELECT rtrim(path, '/') AS normalised FROM file_paths WHERE dir_id = 10;

→'/usr/local/bin'

⚠Anti-Pattern— Treating the Y argument as a literal suffix string

rtrim(col, '.json') is often written expecting to remove the exact file extension '.json', but Y is a character set — the characters '.', 'j', 's', 'o', and 'n' are each stripped individually from the right. So rtrim('noodles.json', '.json') returns 'needl' because all those characters happen to appear at the end, not just the extension.

✓ Instead: To strip a known literal suffix, test with substr() and length(): CASE WHEN substr(col, -5) = '.json' THEN substr(col, 1, length(col)-5) ELSE col END.

rtrim('hello world', 'dlrow') removes every trailing d, l, o, r, w, or space — it does NOT strip the literal suffix 'dlrow'. Characters are removed right-to-left as long as each character is a member of the set. To strip a literal suffix, use CASE with substr() or a combination of length() and instr().

◆SQLite Note

Identical semantics to PostgreSQL's rtrim(string, characters). Both treat the second argument as an unordered set of characters rather than a literal string. The single-argument forms trim trailing spaces in both databases.

soundex

SQLite 3.x+ (compile-time optional)→ TEXT

Returns the four-character Soundex phonetic encoding of string X, or '?000' if X is NULL or contains no ASCII alphabetic characters.

Signature

soundex(X)

Parameters

Parameter	Type	Description
X	TEXT	The string to encode phonetically using the American Soundex algorithm.

Examples

Standard Soundex encoding of a name

sql

SELECT soundex('Robert');

→'R163'

Similar-sounding names produce the same code

sql

SELECT soundex('Rupert');

→'R163'

Variant spellings yield the same Soundex code

sql

SELECT soundex('Smith'), soundex('Smythe');

→'S530' | 'S530'

NULL input returns the sentinel value '?000'

sql

SELECT soundex(NULL);

→'?000'

Practical: fuzzy name search that matches phonetic variants

sql

SELECT id, name FROM customers WHERE soundex(name) = soundex('Johnson') AND city = 'Austin';

→7 | 'Jonson'

⚠Anti-Pattern— Relying on soundex() for non-English or non-ASCII names

The Soundex algorithm was designed for Anglo-American surnames and only processes ASCII alphabetic characters. Names from other languages — such as Nguyen, Kowalczyk, or Papadopoulos — produce codes that are effectively meaningless and will miss obvious phonetic variants. Non-ASCII characters are silently ignored, making the encoding worse the more international the data is.

✓ Instead: For multilingual or international name matching, use the FTS5 spellfix1 extension or a dedicated application-level phonetic library that supports the relevant language family rather than relying on soundex().

Soundex alone is a coarse filter — many unrelated names share a code. Use WHERE soundex(col) = soundex(?) as a fast pre-filter, then refine with an edit-distance or LIKE check. For production fuzzy search, consider the FTS5 spellfix extension, which implements a more sophisticated phonetic algorithm.

◆SQLite Note

soundex() is an OPTIONAL compile-time feature in SQLite, enabled only when built with the SQLITE_SOUNDEX compile-time option. Most SQLite distributions (including the CLI) do not include it. Check availability at runtime with SELECT sqlite_compileoption_used('SOUNDEX'). PostgreSQL does not have a built-in soundex() either — it requires the fuzzystrmatch extension (CREATE EXTENSION fuzzystrmatch).

⚙ Availability: Requires SQLITE_SOUNDEX compile-time option

substr

SQLite 3.x+ (substring alias: 3.34.0+)→ TEXT

Returns a substring of X starting at the Y-th character (1-based), optionally limited to Z characters; if Y is negative, counting starts from the end of the string.

Signatures

substr(X, Y)

substr(X, Y, Z)

Parameters

Parameter	Type	Description
X	TEXT \| BLOB	The source string or BLOB to extract from.
Y	INTEGER	The 1-based start position; negative values count back from the end of the string.
Z	INTEGER	Optional number of characters (or bytes for BLOBs) to return; if negative, returns abs(Z) characters ending just before position Y.

Examples

Extract from position 7 to end of string

sql

SELECT substr('hello world', 7);

→'world'

Extract first 5 characters (positions 1–5)

sql

SELECT substr('hello world', 1, 5);

→'hello'

Negative Y counts from the end of the string

sql

SELECT substr('hello world', -5);

→'world'

Extract 3 characters starting at position 7

sql

SELECT substr('hello world', 7, 3);

→'wor'

Practical: extract prefix before the first hyphen in a SKU string

sql

SELECT substr(sku, 1, instr(sku, '-') - 1) AS category_code FROM products WHERE catalog_id = 2;

→'WIDGET'

⚠Anti-Pattern— Using hardcoded positional offsets instead of instr() for dynamic data

Writing substr(col, 6, 3) to extract a segment assumes the surrounding data is always at a fixed position. When the format varies — different-length prefixes, optional fields, or locale-specific date formats — the hardcoded offsets silently extract the wrong characters without raising any error.

✓ Instead: Use instr() to locate the delimiter dynamically and derive the offset: substr(col, instr(col, '-') + 1) adapts to variable-length prefixes and will return an empty string rather than wrong data if the delimiter is absent.

substr(col, -4) always returns the last 4 characters regardless of string length — cleaner than substr(col, length(col)-3). Combine with instr() for dynamic extraction: to get everything after the last slash, use substr(path, instr(path,'/')+1) (though for multiple slashes you will need a recursive approach).

◆SQLite Note

SQLite uses 1-based indexing (first character is position 1), the same as PostgreSQL's substr(). Negative Y counts from the end, which is a SQLite-specific extension not available in PostgreSQL's substr(). If Y is 0 in SQLite it is treated as 1. The alias substring() (SQL-standard syntax) was added in SQLite 3.34.0; in PostgreSQL, substring() supports both the substr-style call and the SQL standard 'substring(x FROM y FOR z)' syntax.

substring

SQLite 3.34.0+→ TEXT

An alias for substr(); returns a substring of X starting at the Y-th character (1-based), optionally limited to Z characters.

Signatures

substring(X, Y)

substring(X, Y, Z)

Parameters

Parameter	Type	Description
X	TEXT \| BLOB	The source string or BLOB to extract from.
Y	INTEGER	The 1-based start position; negative values count back from the end of the string.
Z	INTEGER	Optional number of characters to return; if negative, returns abs(Z) characters ending just before position Y.

Examples

SQL-standard alias for substr — same result

sql

SELECT substring('hello world', 7);

→'world'

Extract first 5 characters

sql

SELECT substring('hello world', 1, 5);

→'hello'

Extract version number substring

sql

SELECT substring('SQLite 3.44', 8);

→'3.44'

Parse components from a fixed-format date string

sql

SELECT substring('2024-01-15', 1, 4) AS year, substring('2024-01-15', 6, 2) AS month;

→'2024' | '01'

Practical: extract email domain from address column

sql

SELECT id, substring(email, instr(email,'@')+1) AS domain FROM users WHERE active = 1;

→1 | 'example.com'

⚠Anti-Pattern— Attempting the SQL-standard FROM/FOR syntax in SQLite

Developers porting queries from PostgreSQL often write substring(col FROM 3 FOR 5) expecting it to work in SQLite because substring() is present. SQLite does not support the FROM/FOR keyword syntax — it only accepts the function-call form substring(col, 3, 5) — so the FROM/FOR variant raises a parse error that can be confusing given that the function name itself is recognised.

✓ Instead: Always use the function-call form substring(col, start, length) in SQLite; reserve the FROM/FOR syntax for PostgreSQL-specific queries or ORM layers that translate between dialects.

Both substr() and substring() compile to the same bytecode in SQLite, but substring() matches the SQL standard spelling and is also valid in PostgreSQL (with FROM/FOR syntax too). Writing substring() makes your queries easier to port and is immediately recognisable to developers coming from other SQL dialects.

◆SQLite Note

substring() was added as an alias for substr() in SQLite 3.34.0 (2020-12-01). In PostgreSQL, substring() supports both the function-call form substring(str, start, len) and the SQL-standard form substring(str FROM start FOR len). SQLite only supports the function-call form; the FROM/FOR syntax is not valid in SQLite.

trim

SQLite 3.x+→ TEXT

Returns X with leading and trailing spaces removed; if Y is supplied, removes any characters found in Y from both ends of X.

Signatures

trim(X)

trim(X, Y)

Parameters

Parameter	Type	Description
X	TEXT	The source string to trim.
Y	TEXT	Optional set of characters to strip from both the left and right ends of X.

Examples

Remove leading and trailing spaces

sql

SELECT trim('  hello  ');

→'hello'

Remove a specific character from both ends

sql

SELECT trim('---hello---', '-');

→'hello'

Y is a character set — any combination of x, y, z is stripped

sql

SELECT trim('xyzhelloyzx', 'xyz');

→'hello'

Useful in WHERE clauses for comparing padded input

sql

SELECT trim('  hello  ') = 'hello';

→1

Practical: clean user-submitted text before storage

sql

SELECT trim(raw_input) AS cleaned FROM form_submissions WHERE submitted_at > '2024-01-01';

→'user@example.com'

⚠Anti-Pattern— Expecting trim() to strip a whole word or multi-character sequence

trim(col, 'end') is often written expecting to remove the word 'end' from both sides of a string, but the Y argument is a character set — each of 'e', 'n', and 'd' is stripped individually. So trim('blender', 'end') returns 'blr' because b-l-e-n-d-e-r has those characters peeled from both ends until a character outside the set is reached.

✓ Instead: To strip a specific word or multi-character sequence from both ends, combine ltrim and rtrim logic with CASE/substr checks, or handle the transformation in application code where string operations are clearer.

Whitespace hidden in stored strings causes hard-to-debug comparison failures. Apply trim() when inserting/updating (or use a CHECK constraint or trigger), not just at query time. A function-based index on trim(col) lets you query efficiently even against pre-existing dirty data.

◆SQLite Note

SQLite's trim(X, Y) treats Y as an unordered set of characters to strip — identical to PostgreSQL's btrim(string, characters). PostgreSQL uses trim(LEADING/TRAILING/BOTH ... FROM ...) as the standard SQL syntax; SQLite does not support the FROM keyword syntax. Use ltrim() or rtrim() in SQLite for one-sided trimming.

unhex

SQLite 3.41.0+→ BLOB

Decodes a hexadecimal string X and returns the corresponding BLOB; returns NULL if X contains any non-hex characters not in the optional ignore-set Y.

Signatures

unhex(X)

unhex(X, Y)

Parameters

Parameter	Type	Description
X	TEXT	A string of hexadecimal digit pairs (upper or lower case); digits must appear in adjacent pairs.
Y	TEXT	Optional set of non-hex characters to silently ignore within X (e.g., spaces, dashes in a UUID string).

Examples

Decode hex to BLOB (the text 'Hello' as bytes)

sql

SELECT unhex('48656C6C6F');

→X'48656c6c6f'

Cast the BLOB back to text to see the decoded string

sql

SELECT CAST(unhex('48656C6C6F') AS TEXT);

→'Hello'

Spaces in X are ignored because ' ' is in the Y ignore-set

sql

SELECT unhex('48 65 6C 6C 6F', ' ');

→X'48656c6c6f'

Invalid hex characters not in Y cause NULL to be returned

sql

SELECT unhex('ZZZZ');

→NULL

Practical: convert a UUID string to raw bytes by stripping dashes first

sql

SELECT unhex(replace(uuid_col, '-', '')) FROM tokens WHERE user_id = 42;

→X'550e8400e29b41d4a716446655440000'

⚠Anti-Pattern— Not validating input before calling unhex() and ignoring silent NULLs

unhex() returns NULL without any error or warning when the input contains an invalid hex character that is not in the ignore-set Y. Code that stores or processes the result without a NULL check will silently discard the decoded value, leading to missing data or unexpected NULLs in downstream columns and queries.

✓ Instead: Always check the result of unhex() with IS NOT NULL before using it, and validate that the input string contains only valid hex characters (and permitted separators) before passing it to the function.

Instead of calling replace() to strip dashes from a UUID before unhex(), pass '-' as the Y argument: unhex(uuid_col, '-'). This is both more concise and slightly faster. The Y argument is useful for any delimited hex format: MAC addresses (unhex(mac, ':')), hex dumps with spaces, etc.

◆SQLite Note

unhex() was added in SQLite 3.41.0 (2023-02-21) — it does not exist in older SQLite versions. PostgreSQL does not have an unhex() function; the equivalent is decode(str, 'hex'), which returns bytea. The two-argument form unhex(X, Y) for ignoring separator characters is a SQLite-specific extension with no PostgreSQL equivalent.

unicode

SQLite 3.7.15+→ INTEGER

Returns the numeric Unicode code point (integer) of the first character in string X.

Signature

unicode(X)

Parameters

Parameter	Type	Description
X	TEXT	The string whose first character's Unicode code point is returned; the result is unspecified if X is not a string.

Examples

ASCII 'A' has code point 65

sql

SELECT unicode('A');

→65

Only the first character is examined

sql

SELECT unicode('Hello');

→72

Non-ASCII character — U+00E9 LATIN SMALL LETTER E WITH ACUTE

sql

SELECT unicode('é');

→233

Emoji code point (U+1F600)

sql

SELECT unicode('😀');

→128512

Practical: advance a letter by one position using unicode() and char()

sql

SELECT char(unicode(letter) + 1) AS next_letter FROM alphabet WHERE pos = 5;

→'F'

⚠Anti-Pattern— Passing a multi-character string expecting codes for all characters

unicode('Hello') returns 72 — the code point of 'H' only. Developers who expect it to return an array of code points, or who pass a whole column value hoping to validate every character, silently get only the first character's code. The remaining characters are completely ignored with no warning.

✓ Instead: To inspect all characters in a string, iterate character by character using substr() in a recursive CTE or handle the logic in application code; unicode() is strictly a single-character function.

unicode() and char() are inverses of each other. You can implement ROT-13 or simple Caesar ciphers, validate that all characters fall within a specific Unicode block, or sort strings by their code point values. For example, check if every character is ASCII printable with WHERE unicode(col) BETWEEN 32 AND 126.

◆SQLite Note

SQLite's unicode(X) is equivalent to PostgreSQL's ascii(X) for ASCII characters (both return the code point of the first character), but unicode() correctly handles full Unicode beyond U+007F whereas PostgreSQL's ascii() is limited to code points 0–255. PostgreSQL uses the separate function chr() (and ascii()) while SQLite uses char() (and unicode()).

upper

SQLite 3.x+→ TEXT

Returns a copy of string X with all ASCII lowercase letters converted to their uppercase equivalents.

Signature

upper(X)

Parameters

Parameter	Type	Description
X	TEXT	The string to convert to uppercase.

Examples

Basic ASCII uppercase conversion

sql

SELECT upper('hello world');

→'HELLO WORLD'

Mixed alphanumeric — digits and punctuation unchanged

sql

SELECT upper('sqlite 3.44');

→'SQLITE 3.44'

Non-ASCII character é is NOT uppercased without the ICU extension

sql

SELECT upper('café');

→'CAFé'

Capitalise first letter only (poor-man's initcap)

sql

SELECT upper(substr(name, 1, 1)) || substr(name, 2) AS capitalised FROM tags;

→'Widget'

Practical: case-insensitive ISO code lookup without changing stored data

sql

SELECT * FROM countries WHERE upper(iso_code) = upper(:code);

→-- matching row

⚠Anti-Pattern— Using upper(col) = upper(?) in a WHERE clause without a functional index

Wrapping a column in upper() inside a WHERE clause prevents SQLite from using a standard B-tree index on that column, causing a full table scan on every lookup. This is functionally correct but silently unscalable — it works fine in development with small tables and only becomes apparent as row counts grow into the hundreds of thousands.

✓ Instead: Create a functional index — CREATE INDEX idx_upper_col ON tbl(upper(col)); — and consistently query with WHERE upper(col) = upper(:param) so the index is engaged and scans are avoided.

CREATE INDEX idx_iso_upper ON countries(upper(iso_code)); then query WHERE upper(iso_code) = upper(:code). The index is used automatically. This approach also works with lower() — pick one convention and stick to it throughout the schema.

◆SQLite Note

SQLite's built-in upper() only folds ASCII letters (a–z to A–Z). Characters outside ASCII — accented letters, Greek, Cyrillic, emoji — are returned unchanged. PostgreSQL's upper() handles full Unicode case folding natively. To get Unicode-aware uppercasing in SQLite, compile with or dynamically load the ICU extension.

Related SQLite Categories

Core Functions →JSON Functions →