Posted by decipherinfosys on July 30, 2007
In Oracle, one can use the INSTR function to search for a string within another string. If there is a match, then it returns the starting position of the matching string. If there is no match, it returns 0. It can also find the specific starting point of the search string within the string. If the starting position is negative, then search will be performed from the end of the string. INSTR takes four arguments. First two are mandatory and last two arguments are optional.
INSTR(<Actual String>, <Search String>, <Starting Position>, <Occurrence>)
<Search String> is the string or expression to be to be searched.
<Actual String> is the string or expression in which we need to look for search string.
<Starting Position> position from which search should start. This is an optional. If it is not given search starts from starting position of <Actual String>
<Occurrence> This is also an optional argument. If given, it will look for specific occurrence of the search string within the string.
First let us create a table and populate it with data.
CREATE TABLE INPT_STATE
— Tablespace Clause
INSERT INTO INPT_STATE VALUES(‘GEORGIA,USA’);
INSERT INTO INPT_STATE VALUES(‘TEXAS,USA’);
INSERT INTO INPT_STATE VALUES(‘CALIFORNIA,USA’);
INSERT INTO INPT_STATE VALUES(‘ALBERTA,CANADA’);
INSERT INTO INPT_STATE VALUES(‘ONTARIO,CANADA’);
Following SQL will extract the state and country separately using INSTR function.
SELECT substr(state,1,instr(state,’,’)-1) as State,
substr(state,instr(state,’,’)+1, length(state) – instr(state,’,’)) as Country
Here is the output of the query.
Now let us add one more record to see how we can extract country.
INSERT INTO INPT_STATE VALUES(‘MIAMI,FLORIDA,USA’);
In order to extract country from above record, we have to use INSTR function with starting position and occurrence arguments. Here is the SQL.
SELECT substr(state,instr(state,’,’,1,2)+1, length(state) – instr(state,’,’,1,2))
WHERE state like ‘MIAMI%’
In above statement 1 is starting position of the string and 2 is occurrence of the search string. Since country ‘USA’ is after second comma, we are doing substring after second occurrence of the search string (‘,’) to get the desired result.
One Response to “INSTR function”
Sorry, the comment form is closed at this time.