SQLite INSTR() Function

The SQLite INSTR() function returns the location of the first occurrence of string Y in the string X. If Y is not found within the string X, this function returns 0.

If the passed argument are both BLOBs, then INSTR(X,Y) returns one more than the number bytes prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X.

If both arguments are non-NULL and are not BLOBs then both are interpreted as strings. If either X or Y are NULL in INSTR(X,Y) then the result is NULL.

Syntax

INSTR(X, Y)

Parameters

Return Value

Returns the location of the first occurrence of the Y in X.

Example 1:

The example below shows the usage of INSTR() function.

SELECT INSTR('AlphaCodingSkills.com', 'A');
Result: 1

SELECT INSTR('AlphaCodingSkills.com', 'Coding');
Result: 6

SELECT INSTR('AlphaCodingSkills.com', '.com');
Result: 18

SELECT INSTR('AlphaCodingSkills.com', 'l');
Result: 2

SELECT INSTR('AlphaCodingSkills.com', 'Z');
Result: 0

SELECT INSTR('AlphaCodingSkills.com', 'ABC');
Result: 0

Example 2:

Consider a database table called Employee with the following records:

In the query below, the INSTR() function is used to extract the country code from the PhoneNumber column records.

SELECT *, SUBSTR(PhoneNumber, 1, INSTR(PhoneNumber, '-') - 1) AS CountryCode 
FROM Employee;

This will produce the result as shown below: