SQLite Instr() Function

  Total Views : 286
  Comments : 0
  Keywords : SQLite instr() function to find the position of given character in string, Get the index location of substring in specified string using 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 substring in specified string with examples.

SQLite Instr() Function

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

 

The SQLite Instr() function will perform Case Sensitive search to return the starting position of substring in 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 string as NULL then also instr() function will return NULL.

 

Here its important to note that instr() function returns starting position of string as 1 not 0. If substring occurs more than one time in given string then instr() function returns index of first occurance of substring in 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 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.

 

sqliteSELECT instr('Welcome to Tutlane','Tutlane');

 

instr("Welcome to Tutlane")

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

26

 

sqliteSELECT instr('Welcome to Tutlane','tutlane');

 

instr("Welcome to Tutlane")

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

0

 

sqliteSELECT 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.

 

sqliteSELECT 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.

 

sqliteSELECT iinstr(i,'a'FROM test;

 

i           instr(i,"a")

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

Shweta      6

Shwets      0

Shwati      4

Vinay       4

Vijay       4

Vivek       0

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

 

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

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User