Menu

SQLite substring(): Syntax, Examples and Difference from substr()

Updated on

SQLite substring(string, start, length) extracts part of a string, starting at a 1-based character position. SQLite added substring() in version 3.34.0 as an alias for substr(). Both functions have the same behavior in supported SQLite versions.

Syntax

substring(X, Y)
substring(X, Y, Z)
  • X is the text or BLOB to extract from.
  • Y is the starting position. 1 selects the first character; a negative position counts backwards from the end.
  • Z is the optional number of characters. When omitted, the substring continues to the end of the input.
  • A negative Z returns characters preceding the start position, not a negative-length error.
  • If any supplied argument is SQL NULL, the result is SQL NULL.

For text values, SQLite counts Unicode characters. For BLOB inputs, it counts bytes.

Examples

Get a word from the middle of a string

SELECT substring('hello world', 7, 5) AS extracted;

extracted
---------
world

Extract the last three characters

SELECT substring('SQLite', -3) AS suffix;

suffix
------
ite

Compare substring() and substr()

SELECT
    substring('database', 1, 4) AS long_name,
    substr('database', 1, 4) AS short_name;

long_name  short_name
---------  ----------
data       data

The two names produce identical results on SQLite 3.34.0 and later. For more cases, including negative lengths and Unicode text, see the SQLite substr() reference.

Common questions

Which SQLite version supports substring()? SQLite 3.34.0 and later. For older SQLite applications, use substr().

Are positions zero-based? No. Positions normally start at 1.

Is SQLite substring() identical to PostgreSQL SUBSTRING? No assumption of portability should be made: SQL dialects differ in their substring syntaxes and edge cases. Verify expressions separately when migrating queries.

Is substring() different from substr() in SQLite? No. They are aliases for the same operation in supported versions.

See the official SQLite core-function documentation. Run the examples in SQLite Playground and compare both spellings in your browser.