Functions Oracle INSTR ,INSTRB , INSTRC , INSTR2 , INSTR4 are for searching your search text or a character in a string and it returns the position number in the string. When searching text didn’t find anything the functions are returning zero (0). The Oracle INSTR syntax is following:
INSTR('<your_string>','<searchible_string>'[,<starting_point>,<occurrence_number>]);
The first example is using string “AAAAAAAALMBBBBBBBBBAAAAAA” and we are looking for text “LM“. The “LM” string starts on the 9th position in the text and the same value is returned by the example below.
SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','LM')
FROM DUAL;
There is possibility to look for your string starting from n-position and the next example will try to look for the same “LM” string from the 10th position. The output return 0 because we do have only one “LM” string starting from the 9th position and the function could not find more matching strings.
SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','LM',10)
FROM DUAL;
The third example is a bit different from the examples above because we are trying to find only one letter “B“. The INSTR function returns 11 because this is the first match and the following “B“-s will be ignored.
SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','B')
FROM DUAL;
You can look for the “B” character in a reverse way or from left to right. To define the search from right to left you need to set starting position a negative number and on the example below you can find starting position as -1. The Oracle INSTR function returned the first “B” character from right to left on position 19th or we can call it as the last “B” in the string.
SELECT INSTR('AAAAAAAALMBBBBBBBBBAAAAAA','B',-1)
FROM DUAL;
The other Oracle INSTR functions are working with the same principles only the output is different.
See Also:
Oracle Select Oracle Substr Oracle Length Oracle TO_CHAR Home