INSTR function
INSTR returns the location (beginning) of a pattern in a given string.
Its simple form is:
INSTR(string, pattern-to-find)The general syntax of INSTR is:
INSTR (string to search, search pattern [, start [,occurrence]])The arguments within brackets ([]) are optional.
SQL> SELECT INSTR('Pattern', 'tt') FROM dual;
INSTR('PATTERN','TT')
---------------------
3
SQL> SELECT INSTR('This is a test','is') FROM dual;
INSTR('THISISATEST','IS')
-------------------------
3
SQL>
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
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
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;
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>