This Oracle tutorial explains how to use the Oracle/PLSQL INSTR function.
INSTR returns the location (beginning) of a pattern in a given string.
Its simple form is:
INSTR(string, pattern-to-find)The syntax for the Oracle/PLSQL INSTR function is:
INSTR( string, substring [, start_position [, nth_appearance ] ] )
string is the string to search, which can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
substring is the substring to search for.
start_position is the start position. This argument is optional. Default to 1.
The first position in the string is 1.
If the start_position is negative,
the INSTR function counts from the end of string and
then searches towards the beginning of string.
nth_appearance is the nth appearance of substring.
This is optional. Default to 1.
If substring is not found in string, then the INSTR function will return 0.
SQL> SELECT INSTR('Pattern', 'tt') FROM dual;
-- from w w w . ja v a 2s . c om
INSTR('PATTERN','TT')
---------------------
3
SQL> SELECT INSTR('This is a test','is') FROM dual;
INSTR('THISISATEST','IS')
-------------------------
3
SQL>
If search pattern is not in the string, the INSTR function returns 0:
SQL> SELECT INSTR('This is a test','abc',1,2) FROM dual;
INSTR('THISISATEST','ABC',1,2)
------------------------------
0
Look for the second occurrence of "is":
SQL> SELECT INSTR('This is a test','is',1,2) FROM dual;
INSTR('THISISATEST','IS',1,2)
-----------------------------
6
Get the position where the second occurrence of the e character occurs, starting from the beginning of the employee name:
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 SAL NUMBER(7, 2),
5 DEPTNO NUMBER(2));
SQL> INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
SQL> INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
SQL> INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250.12, 30);
SQL> INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975.23, 20);
SQL> INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250.23, 30);
SQL> INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
SQL> INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', NULL, 10);
SQL> INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
SQL> INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',NULL, 10);
SQL> INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
SQL> INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 0.99, 20);
SQL> SELECT ename, INSTR(ename, 'E', 1, 2)
2 FROM emp;-- w w w . j a v a 2s .com
ENAME INSTR(ENAME,'E',1,2)
---------- --------------------
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
11 rows selected.
SQL>
SQL>
SQL> SELECT ename, INSTR(ename, 'A', 1, 2)
2 FROM emp;
ENAME INSTR(ENAME,'A',1,2)
---------- --------------------
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 3
11 rows selected.
SQL>
SQL> SELECT ename, INSTR(ename, 'A', 1, 2)
2 FROM emp;
ENAME INSTR(ENAME,'A',1,2)
---------- --------------------
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 3
11 rows selected.
SQL>
Use dates with character functions.
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 HIREDATE DATE);
-- from w w w.j av a2 s. c om
Table created.
SQL> INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981', 'DD-MON-YYYY'));
SQL> INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
SQL> SELECT empno, INSTR(hiredate, 'FEB')
2 FROM emp;
EMPNO INSTR(HIREDATE,'FEB')
---------- ---------------------
1 0
2 4
3 4
4 0
5 0
SQL>