SQLite instr() function

Description

SQLite instr() takes a string and a substring of it as arguments, and returns an integer which indicates the position of the first occurrence of the substring within the string.

• The instr(ori_str,sub_str) function finds the first occurrence of string sub_str within string ori_str and returns the number of prior characters plus 1, or 0 if sub_str is nowhere found within ori_str.
• If ori_str and sub_str are both BLOBs, then instr(ori_str,sub_str) returns one more than the number bytes prior to the first occurrence of sub_str, or 0 if sub_str does not occur anywhere within ori_str.
• If both arguments ori_str and sub_str to instr(ori_str,sub_str) are non-NULL and are not BLOBs then both are interpreted as strings.
• If either ori_str or sub_str is NULL in instr(ori_str,sub_str) then the result is NULL.

Syntax:

instr(ori_str,sub_str)

Arguments

SQLite Version : 3.8.5

Pictorial Presentation

Example: SQLite instr() function

The following SQLite statement finds the first occurrence of 'st' in 'myteststring' at the position 5, it returns 5.

SELECT instr('myteststring','st');

Sample Output:

instr('myteststring','st')
--------------------------
5

Example: SQLite instr() function with WHERE clause

The following SQLite statement returns a list of books (in the first column of the output) if string 'an' is found within the name of the book, and an integer (in the second column of the output) indicating the position of the first occurrence of the string 'an' within the name of the book. A condition placed after the WHERE clause makes sure that it returns only those books, within which, position of the first occurrence of substring 'an' is more than 0.

Sample table: book_mast

SELECT book_name, instr(book_name,'an')
 FROM book_mast 
 WHERE INSTR(book_name,'an')>0; 

Sample Output:

 book_name                            instr(book_name,'an')
 -----------------------------------  ---------------------
 Understanding of Steel Construction  8
 Transfer  of Heat and Mass           3
 Advanced 3d Graphics                 4
 Human Anatomy                        4
 Networks and Telecommunications      10
 

Previous: ifnull()
Next: hex()