Oracle INSTR

Oracle INSTR

The syntax for Oracle INSTR function is as follows:

instr (s1, s2, st, t)

The Oracle INSTR  function is similar to the SUBSTR, except instead of returning the sub string, Oracle INSTR returns the location of the string.

SELECT
  INSTR('Now is the time for all good men',' ',1,3)
FROM
  dual; 

INSTR('NOWISTHETIMEFORALLGOODMEN','',1,3)
-----------------------------------------                                       11

1 row selected.

In the example above, I am using Oracle INSTR to look for the third occurrence of the string " " (a space) starting at the beginning.  According to the results of the Oracle INSTR function, the third space in the string is at character number 11.

Now for a little challenge.  Suppose that my boss wants to know what the first word of every book title is.  Think about the solution before looking at the answer below.

SELECT

  SUBSTR(book_title,1,(INSTR(book_title,' ',1,1)-1)) "First Word"
FROM
  book; 

Basically, I used the Oracle INSTR function and queried a substring of the book title starting at the first character, until the first space, minus one to remove the space from the results.  This type of Oracle INSTR query is actually very common on databases that are not properly normalized.  If the author names were stored in our PUBS database in one column, we would have to use this type of Oracle INSTR query to separate the first and last names when needed.

Oracle 10g Changes to Oracle INSTR

The Oracle INSTR function has been extended with the new function regexp_instr.

regexp_instr extends the functionality of the Oracle INSTR function by letting you search a string for a POSIX regular expression pattern. The function evaluates strings using characters, as defined by the input character set. It returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, the function returns 0.

The Oracle documentation gives us the following example for the INSTR syntax:

With the following description of the above illustration:

{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])

---