INSTR Function in SQL

INSTR

• The instr() function allows you for searching through a string for set of characters.
• To  show position of character of a string.
• It will return interger values

Syntax: instr (string, search_str [, start_chr_count [, occurrence] ])

Ex:

 select instr('information','o',4,1), instr('information','o',4,2) from dual;

output on tool

The query

      position	  1    2    3    4   5   6   7   8  9  10  11
	STRING	  i    n    f    o   r   m   a   t  i   o   n
      position	 -11  -10  -9   -8  -7  -6  -5  -4  -3 -2  -1

instr(‘information’,’o’,4,1′) means –> Its count word ‘o’ . Starting position from 4 TO First position of word ‘o’.

instr(‘information’,’o’,4,2) means –> Its count word ‘o’ . Starting position from 4 TO Second position of word ‘o’.

Some More Example

The query

       position	  1   2    3    4   5   6   7   8   9  
	STRING	  D   E    V    E   L   O   P   E   R  
       position	 -9  -8   -7   -6  -5  -4  -3  -2  -1

select instr('developer','e',3) from dual;
--------------------------------------------
4

instr(‘developer’,’e’,3) means –> Its count word ‘e’ . Starting position from 3 TO First position of word ‘e’.

select instr('developer','e',3,3) from dual;
--------------------------------------------
8

instr(‘developer’,’e’,3.2) means –> Its count word ‘e’ . Starting position from 3 TO 2nd position of word ‘e’.

select instr('developer','e',3,3) from dual;
--------------------------------------------
0

instr(‘developer’,’e’,3,3) means –> Its count word ‘e’ . Starting position from 3 TO 3rd position of word ‘e’. i.e there is not present in third place of word ‘e’ in string ‘DEVELOPER.so output will ‘0’.

select instr('developer','e',-3,2) from dual;
--------------------------------------------
2

instr(‘developer’,’e’,-3,2) means –> Its count word ‘e’ . Starting position from –3 TO 2nd position of word ‘e’.

select instr('developer','e',-3,1) from dual;
--------------------------------------------
4

instr(‘developer’,’e’,-3,1) means –> Its count word ‘e’ . Starting position from –3 TO 1st position of word ‘e’.

select instr('developer','e',-3,3) from dual;
--------------------------------------------
0

instr(‘developer’,’e’,-3,3) means –> Its count word ‘e’ . Starting position from –3 TO 2nd position of word ‘e’.i.e there is not present in third place of word ‘e’ in string ‘DEVELOPER.so output will ‘0’.

Note:-

1.If you are not specifying start_chr_count and occurrence then it will start search from the beginning and finds first occurrence only.

ex

select instr('letter','e') from dual;
--------------------------------------------
2

2.If both parameters start_chr_count and occurrence are null, it will display nothing.

select instr('letter','e','','') from dual;
--------------------------------------------
null