SQLite: instr Function

This SQLite post explains how to use the SQLite instr function with syntax and examples.

Description

The SQLite instr function returns the location of a substring in a string.

Syntax

The syntax for the instr function in SQLite is:

instr( string, substring )

Parameters or Arguments

string

The string to search.

substring

The substring to search for in string.

Note

• The first position in string is 1.
• When finding the location of a substring in a string, the instr function performs a case-sensitive search.
• If substring is not found in string, then the instr function will return 0.
• The instr function was introduced in SQLite 3.7.15.

Applies To

The instr function can be used in the following versions of SQLite:

• SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.15

Example

Let's look at some SQLite instr function examples and explore how to use the instr function in SQLite.

For example:

sqlite> SELECT instr('AODBA.com', 'B');
Output:  4

sqlite> SELECT instr('AODBA.com', 'O');
Output:  2


sqlite> SELECT instr('AODBA.com', 'BA');
Output:  4

sqlite> SELECT instr('AODBA.com', 'Z');
Output:  0

Frequently Asked Questions

Question: I've tried to use the instr function in SQLite but it returns the error:

Error: no such function: instr

I know that the function exists, what do I do?

Answer: The instr function was first introduced in SQLite 3.7.15 so you must be running an older version of SQLite. Not sure what version of SQLite you are running, try the sqlite_version function.

sqlite> SELECT sqlite_version();
Output: '3.7.13'    (your version will vary)

Now that you know that your version of SQLite is older and does not support the instr function, you can either upgrade your version of SQLite to something newer or try rewriting your SQL statement without the instr function, possibly with the LIKE condition.

For example, if you had tried to run the following SQL statement using the instr function:

SELECT *
FROM employees
WHERE instr(last_name, 'e');

You could try rewriting the statement using the LIKE condition as follows:

SELECT *
FROM employees
WHERE last_name LIKE '%e%';

These two statements would return the same results.