Wednesday, February 22, 2012

INSTR

INSTR looks for a series of 1 or more characters in a string. Here is its format:
select instr(string,set,[,start[,occurrence]]) from ...
You can find the position of the first letter t in International as follows:

SQL> select instr('International','t')
  2  from dual
  3  /

INSTR('INTERNATIONAL','T')
--------------------------
                         3

SQL>


... and it works just as well when you are searching for more than 1 character:

SQL> select instr('International','na')
  2  from dual
  3  /

INSTR('INTERNATIONAL','NA')
---------------------------
                          6

SQL>


You can add optional parameters too. This is how you search for na in International starting from position 8:

SQL> select instr('International','na',8)
  2  from dual
  3  /

INSTR('INTERNATIONAL','NA',8)
-----------------------------
                           11

SQL> 


You can get the same answer by looking for the start position of the 2nd occurrence of na in International like this:

SQL> select instr('International','na',1,2)
  2  from dual
  3  /

INSTR('INTERNATIONAL','NA',1,2)
-------------------------------
                             11

SQL>


Notice that, if you wish to include an occurrence parameter, the start parameter is mandatory. An extra comma by itself is not sufficient:

SQL> select instr('International','na',,2)
  2  from dual
  3  /
select instr('International','na',,2)
                                  *
ERROR at line 1:
ORA-00936: missing expression

SQL>

No comments: