RelationalDBDesign RelationalDBDesign


Data Manipulation   «Prev 

Oracle INSTR Function and Syntax

The INSTR function allows for simple or sophisticated searching through a string for a set of characters, not unlike LTRIM and RTRIM, except that INSTR does not clip anything off. It simply tells you where in the string it found what you were searching for. This is similar to the LIKE logical operator. However, LIKE can only be used in a
  1. where or
  2. having
clause, and INSTR can be used anywhere except in the from clause. Of course, LIKE can be used for complex pattern searches that would be quite difficult, if even possible, using INSTR.
Here is the format for INSTR:

INSTR(string,set [,start [,occurrence ] ])


select Author from MAGAZINE;

AUTHOR
-------------------------
BONHOEFFER, DIETRICH
CHESTERTON, G.K.
RUTH, GEORGE HERMAN
WHITEHEAD, ALFRED
CROOKES, WILLIAM

To find the location of the first occurrence of the letter O, INSTR is used without its options and with set as 'O' (note the single quotation marks, since this is a literal), as shown in the following listing:
select Author, INSTR(Author,'O') from MAGAZINE;

AUTHOR INSTR(AUTHOR,'O')
------------------------- -----------------
BONHOEFFER, DIETRICH 2
CHESTERTON, G.K. 9
RUTH, GEORGE HERMAN 9
WHITEHEAD, ALFRED 0
CROOKES, WILLIAM 3

This is, of course, the same as the following:
select Author, INSTR(Author,'O',1,1) from MAGAZINE;

If INSTR had looked for the second occurrence of the letter O, it would have found
select Author, INSTR(Author,'O',1,2) from MAGAZINE;

AUTHOR INSTR(AUTHOR,'O',1,2)
------------------------- ---------------------
BONHOEFFER, DIETRICH 5
CHESTERTON, G.K. 0
RUTH, GEORGE HERMAN 0
WHITEHEAD, ALFRED 0
CROOKES, WILLIAM 4

INSTR found the second O in Bonhoeffer's name, at position 5, and in Crookes' name, at position 4.
Chesterton has only one O, so for him, Ruth, and Whitehead, the result is zero, meaning no success and no second O was found.
To tell INSTR to look for the second occurrence, you also must tell it where to start looking (in this case, position 1). The default value of start is 1, which means that is what it uses if you do not specify anything, but the occurrence option requires a start, so you have to specify both. If set is not just one character but several, INSTR gives the location of the first letter of the set, as shown here:
select Author, INSTR(Author,'WILLIAM') from MAGAZINE;

AUTHOR INSTR(AUTHOR,'WILLIAM')
------------------------- -----------------------
BONHOEFFER, DIETRICH 0
CHESTERTON, G.K. 0
RUTH, GEORGE HERMAN 0
WHITEHEAD, ALFRED 0
CROOKES, WILLIAM 10

This has many useful applications, such as in the MAGAZINE table, for instance:
select Author, INSTR(Author,',') from MAGAZINE;

AUTHOR INSTR(AUTHOR,',')
------------------------- -----------------
BONHOEFFER, DIETRICH 11
CHESTERTON, G.K. 11
RUTH, GEORGE HERMAN 5
WHITEHEAD, ALFRED 10
CROOKES, WILLIAM 8

The syntax of the INSTR function, including parameters. If the character or characters are not found, the INSTR function returns zero.

This example shows how to search for the letter "a" in the LASTNAME column of the CUSTOMER table. The searching used in this function is case-sensitive. In this case, the default starting position and occurrence number were used because these two parameters were omitted.

Add a position number as the third parameter to start the search for the character at some point within the field. For example, if you want to find the word "the" in the LOG_TEXT column, but only in the 20th character or beyond, the query would look like the one shown here. The identical function was added to the WHERE clause to limit the rows returned to those that match the INSTR function.
SELECT INSTR(UPPER(LOG_TEXT), 'FISH', 20) POS,
LOG_TEXT
FROM PET_CARE_LOG
WHERE INSTR(UPPER(LOG_TEXT), 'FISH', 20) > 0 

You can find the second, third, or other occurrence of the character by adding a third parameter to the function. For example, if you wish to find the second occurrence of the word "the" in the LOG_TEXT column, the query would be revised to include both the starting position (20) and the occurence number (2) as shown here.

You can use an INSTR function to set the starting position of another INSTR function. In this example, we have replaced the starting position (20) with an INSTR function that looks for period (.). In effect, we are setting the starting position at the end fo the first sentence in the text.