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.
INSTR('Now is the time for all good men',' ',1,3)
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.
SUBSTR(book_title,1,(INSTR(book_title,' ',1,1)-1)) "First Word"
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 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
With the following description of the above illustration:
(string , substring [, position [, occurrence ] ])