SQLite String Functions

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().

SELECT concat(first_name, ' ', last_name) AS full_name FROM users WHERE dept = 'Sales';

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() 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.

SELECT concat_ws(', ', city, state, country) AS address_line FROM locations WHERE id = 1;

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.

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.

SELECT format('Invoice #%05d — Total: $%.2f', invoice_id, amount) AS label FROM invoices LIMIT 1;

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(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.

SELECT id, hex(thumbnail_data) AS hex_preview FROM media WHERE length(thumbnail_data) < 64;

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.

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.

SELECT email, substr(email, instr(email, '@') + 1) AS domain FROM users;

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.

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.

SELECT id, body, length(body) AS char_count FROM posts WHERE length(body) > 280;

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.

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.

SELECT * FROM products WHERE name LIKE '%wireless%' ESCAPE '\';

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.

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.

SELECT email FROM users WHERE lower(email) = lower(:input_email);

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('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.

SELECT ltrim(code, '0') AS trimmed_code FROM barcodes WHERE ltrim(code,'0') != code;

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.

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.

SELECT filename, octet_length(content) AS bytes FROM files WHERE octet_length(content) > 1048576;

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.

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).

SELECT printf('%s has %d item(s) totalling $%.2f', customer, qty, total) FROM order_summary LIMIT 1;

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.

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.

UPDATE articles SET body = replace(body, 'colour', 'color') WHERE body LIKE '%colour%';

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('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().

SELECT rtrim(description, '. ') AS clean_desc FROM products WHERE description LIKE '%. ';

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 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.

SELECT * FROM customers WHERE soundex(last_name) = soundex('Johnson');

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).

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).

SELECT substr(iban, 1, 2) AS country_code, substr(iban, 3, 2) AS check_digits FROM bank_accounts;

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.

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.

SELECT substring(phone FROM 1 FOR 3) AS area_code FROM contacts;

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.

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.

SELECT trim(user_input) AS cleaned FROM form_submissions WHERE trim(user_input) != user_input;

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.

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.

SELECT unhex('48656C6C6F') AS decoded;

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() 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.

SELECT char, unicode(char) AS codepoint FROM (SELECT substr('café',n,1) AS char FROM (VALUES(1),(2),(3),(4),(5)));

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()).

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.

SELECT upper(country_code) AS normalised FROM shipping_addresses WHERE upper(country_code) != country_code;

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.