SQLite INSTR

The SQLite INSTR searches a substring in a string and returns an integer that indicates the position of the substring, which is the first character of the substring.

If the substring does not appear in the string, the INSTR function returns 0.  In case either string or substring is NULL, the INSTR function returns a NULL value.

Note that the INSTR function also works with the BLOB data type.

Syntax

The following statement shows the syntax of the INSTR function:

INSTR(string, substring);Code language: SQL (Structured Query Language) (sql)

Arguments

The INSTR function accepts two arguments.

string

is the source string that INSTR function searches for the substring

substring

is the substring that is used to search.

Return Type

Integer

Examples

The following example searches the string SQLite Tutorial for the substring Tutorial. It returns the position in SQLite Tutorial at which the first occurrence of the Tutorial begins.

SELECT INSTR('SQLite Tutorial','Tutorial') position;Code language: SQL (Structured Query Language) (sql)

position
------------
8

The INSTR searches for the substring case-sensitively. For example, the following statement returns the first occurrence of the substring I not i.

SELECT INSTR('SQLite INSTR', 'I');Code language: SQL (Structured Query Language) (sql)

The following statement selects the employees table and returns addresses that contain the SW substring.

SELECT lastname,
       firstname,
       address,
       INSTR(address, 'SW') sw
  FROM employees
 WHERE sw > 0;Code language: SQL (Structured Query Language) (sql)

It is equivalent to the following statement that uses the LIKE operator:

SELECT lastname,
       firstname,
       address
  FROM employees
 WHERE address LIKE '%SW%';Code language: SQL (Structured Query Language) (sql)