In SQLite, you can use the instr() function to return the position of a given character within a string.

The function only returns the position of the first occurrence of the character (if any).

If the character isn’t found, then it returns 0.

If either of the arguments are NULL, then it returns NULL.

How it Works

The instr() function accepts two arguments. The first argument is the string, and the second argument is the character you wish to find within that string.

If the arguments are both BLOBs, then it returns one more than the number bytes prior to the first occurrence of the second argument, or 0 if it does not occur anywhere within the first argument.

The syntax goes like this:

inst(X,Y)

• X is the string
• Y is the character

Example

Here’s an example to demonstrate.

SELECT instr('Black cat', 'a');

Result:

3

Here, I wanted the location of the letter a.

In this case the letter appears twice, and instr() returns the location of the first occurrence.

A Database Example

In this example, I’m going to use the following table:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  139.5     
2           Blue Widget    10.0      
3           Red Widget     10.0      
4           Green Widget   10.0      
5           Widget Stick   89.75     
6           Foo Cap        11.99     

I’m going to search the ProductName column for a letter.

SELECT 
  ProductName,
  instr(ProductName, 'd') 
FROM Products;

Result:

ProductName    instr(ProductName, 'd')
-------------  -----------------------
Widget Holder  3                      
Blue Widget    8                      
Red Widget     3                      
Green Widget   9                      
Widget Stick   3                      
Foo Cap        0                               

Note that the last row didn’t have the letter and so 0 was returned for that row.

NULL Values

As mentioned, if any of the arguments are NULL, then NULL is returned.

SELECT 
  instr(NULL, 'a'),
  instr('Black cat', NULL);

Result:

instr(NULL, 'a')  instr('Black cat', NULL)
----------------  ------------------------