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:
Post a Comment