SQLite Instr() Function

Here we will learn Instr() function in SQLite and how to use SQLite Instr() function to return the index position of or starting position of a substring in the specified string with examples.

SQLite Instr() Function

In SQLite Instr() function is used to search for the substring value in a specified string and return the starting position or index position of a substring in the specified string.

 

The SQLite Instr() function will perform Case Sensitive search to return the starting position of a substring in the specified string. In case, if substring value not found in given string then Instr() function will return “0”.

 

If we give substring values as NULL then instr() function will return NULL value same way if we give a string as NULL then also instr() function will return NULL.

 

Here it is important to note that instr() function returns starting position of string as 1 not 0. If substring occurs more than one time in a given string then instr() function returns the index of the first occurrence of a substring in the specified string.

Syntax of SQLite Instr() Function

Following is the syntax of SQLite instr() function to return the index position of substring in specified string.

 

instr(string, substring)

In above SQLite instr() function syntax we defined few properties those are

 

string – Its source string to search for substring value.

 

substring – Its substring which is used to search for in string.

 

Now we will see how to use SQLite instr() function to get the index position of substring in a specified string with examples.

SQLite Instr() Function Examples

Following are the simple examples of using SQLite Instr() function to get the starting position of substring.

 

sqlite> SELECT instr('Welcome to Tutlane','Tutlane');

 

instr("Welcome to Tutlane")

---------------------------

26

 

sqlite> SELECT instr('Welcome to Tutlane','tutlane');

 

instr("Welcome to Tutlane")

---------------------------

0

 

sqlite> SELECT instr('Welcome to tutlane','t');

 

instr("Welcome to Tutlane")

---------------------------

9

SQLite Instr() Function with Tables

Now we will see how to use instr() function with tables for that create table called “test” and insert some data like as shown following.

 

sqlite> SELECT * from test;

 

i

----------

Shweta

Shwets

Shwati

Vinay

Vijay

Vivek

Now write the query like as shown following to get the index of character “a” using instr() function.

 

sqlite> SELECT i, instr(i,'a') FROM test;

 

i           instr(i,"a")

----------  ------------

Shweta      6

Shwets      0

Shwati      4

Vinay       4

Vijay       4

Vivek       0

If you observe the above result, the instr() function returns the index of  “a” character for all the records. It returns 0 wherever it doesn’t find “a” character.

 

This is how we can use the SQLite instr() function to get the index position of a substring in defined string based on our requirements. 

Previous
 
Next