How To Use Oracle INSTR Function

How To Use Oracle INSTR Function

December 11, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system database oracle performance tuning oracle instr oracle instr in oracle database administrator database

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;

dba oracle dba database management system database oracle performance tuning oracle instr oracle instr in oracle database administrator database

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;

dba oracle dba database management system database oracle performance tuning oracle instr oracle instr in oracle database administrator database

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;

dba oracle dba database management system database oracle performance tuning oracle instr oracle instr in oracle database administrator database

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*