How To Use Oracle INSTR Function

This online tutorial is based on examples to be easier to follow. Functions Oracle INSTR ,INSTRB , INSTRC , INSTR2 , INSTR4 are allowing to search a text or a character in a string and they are returning the character position in a number. When searching text couldn’t be found then the functions are returning zero (0) . The syntax of Oracle Instr is following:

INSTR('<the main string>',
      '<a text to search>',
      [<starting position number>],[<occurrence number>])

In this first Oracle Instr example the function will look for letters “LM” in a string.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','LM')
  FROM DUAL;

The “LM” letters starting position is on the 9th character and the Instr function returns number 9. The second Oracle DBA example will start look for letters “LM” from position 10th and since in the text we have only one group of “LM” letters the Oracle Instr returns number 0.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','LM',10)
  FROM DUAL;

Lets try to find only one letter now. The following SQL query will look for char “B” in string “AAAAAAAALMBBBBBBBBBAAAAAA“.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','B')
  FROM DUAL;

The first “B” letter is sitting on the 11th position if you will start to count from left to right.

In Oracle Instr function you also can look for a string from right to left or as it is called Oracle Instr in reverse way.  To do it you will need to use a negative number as the third parameter in the function. The third parameters is a position parameter and that way Oracle Instr knows to start the process in reverse way from left to right.

SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','B',-1)
  FROM DUAL;

As you see from the last example output the Oracle Instr function returned the “B” letter position number as 19th. The 19th character in this string is the last “B” and it proves the Instr working in reverse way.

See Also:
Oracle Select Home