SQLite instr

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

Name Description
ori_str The string to be searched.
sub_str The string to be searched for within the ori_str.

SQLite Version : 3.8.5

Pictorial Presentation

SQLite INSTR() 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.

  1. SELECT instr('myteststring','st');  
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

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
| BK011   | The Experimental Analysis of Cat    | 0000979011  | CA004   | AUT011 | P005   | 2007-06-09 | French   |     225 |      95.00 |
| BK012   | The Nature  of World                | 0000979012  | CA004   | AUT005 | P008   | 2005-12-20 | English  |     350 |      88.00 |
| BK013   | Environment a Sustainable Future    | 0000979013  | CA004   | AUT012 | P001   | 2003-10-27 | German   |     165 |     100.00 |
| BK014   | Concepts in Health                  | 0000979014  | CA005   | AUT013 | P004   | 2001-08-25 | NULL     |     320 |     180.00 |
| BK015   | Anatomy & Physiology                | 0000979015  | CA005   | AUT014 | P008   | 2000-10-10 | Hindi    |     225 |     135.00 |
| BK016   | Networks and Telecommunications     | 00009790_16 | CA003   | AUT015 | P003   | 2002-01-01 | French   |      95 |      45.00 |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
  1. SELECT book_name, instr(book_name,'an')  
  2.  FROM book_mast   
  3.  WHERE INSTR(book_name,'an')>0;  
 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