Systems Engineering and RDBMS

INSTR function

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
(
STATE VARCHAR2(50)
)
— 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
FROM inpt_state;

Here is the output of the query.

State Country
—— ———-
GEORGIA USA
TEXAS USA
CALIFORNIA USA
ALBERTA CANADA
ONTARIO CANADA

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))
FROM inpt_state
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”

  1. […] by decipherinfosys on October 8th, 2007 In of our previous blog post, we had discussed the INSTR function and how it can be used for finding out the string within […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: