Oracle: search a string for the occurance of a substring 

Joined:
04/09/2007
Posts:
775

March 23, 2009 23:02:57    Last update: March 23, 2009 23:07:28
Oracle function instr searches the occurrence of a sub-string in a string. It returns the index of the first match. It is similar to the Java String.indexOf function, with the exception that the index is 1 based, not 0 based.

-- search for OR from the beginning, returns 2
select instr('CORPORATE FLOOR', 'OR') from dual;

-- search for OR from position 3, returns 5
select instr('CORPORATE FLOOR', 'OR', 3) from dual;

-- search for OR from position 3, second occurence, returns 14
select instr('CORPORATE FLOOR', 'OR', 3, 2) from dual;

-- search for ORE, no match, returns 0
select instr('CORPORATE FLOOR', 'ORE', 3, 2) from dual;
Share |
| Comment  | Tags