Menu

SQLite instr(): Find Substring Position in Text or BLOBs

Updated on

SQLite instr(X, Y) returns the 1-based position of the first occurrence of Y inside X. If Y is not found, it returns 0. Use it to locate text within a column or find a byte sequence inside a BLOB.

The argument order matters: instr(text_to_search, substring_to_find). It is the opposite of functions that place the search term first.

Syntax

instr(X, Y)
  • X is the string or BLOB to search within.
  • Y is the substring or byte sequence to find.
  • The first character (or byte) has position 1.
  • If the substring is absent, the result is 0.
  • If either argument is SQL NULL, the result is SQL NULL.
  • If both are BLOBs, offsets are measured in bytes; otherwise, non-NULL arguments are interpreted as text.

Examples

Find a substring or detect that it is missing

SELECT
    instr('hello', 'll') AS found_position,
    instr('hello', 'xyz') AS not_found,
    instr('hello', '') AS empty_pattern;

found_position  not_found  empty_pattern
--------------  ---------  -------------
3               0          1

An empty search string returns 1, because it matches at the beginning of the input. Do not confuse that with 0, which means no match.

Extract text after a delimiter

Combine instr() with substr() to extract everything following the first dash:

WITH items(code) AS (
    VALUES ('AB-123'), ('NO_DASH')
)
SELECT
    code,
    CASE
        WHEN instr(code, '-') > 0
        THEN substr(code, instr(code, '-') + 1)
        ELSE NULL
    END AS suffix
FROM items;

code     suffix
--------  ------
AB-123    123
NO_DASH   NULL

Checking for 0 before extracting is important: without the guard, a missing delimiter could silently produce the wrong substring.

Unicode characters versus BLOB bytes

SELECT
    instr('café', 'é') AS character_position,
    instr(X'00410241', X'0241') AS blob_byte_position;

character_position  blob_byte_position
------------------  ------------------
4                   3

Text positions are based on characters, not UTF-8 byte offsets. When both inputs are BLOBs, instr() counts bytes from position 1.

Common questions

What does instr() return if nothing matches? 0, not NULL. SQL NULL is returned if an argument itself is NULL.

Can I use instr() with an empty search term? Yes. SQLite returns 1.

Does instr() support regular expressions? No. It finds the first occurrence of a literal substring or byte sequence; it does not use a regex pattern.

Can I find the last occurrence? instr() returns the first occurrence. For more complex extraction, combine it with other text functions or application logic.

For the authoritative contract, see SQLite instr() documentation. You can run and adapt the SQL in SQLite Playground.