Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. Single Row Character Functions
  5. INSTR

INSTR

INSTR function returns the position of ‘Expr’ in the String, beginning from the ‘m’ character in the input string. Parameter ‘n’ is optional and specifies the occurrence to be returned by INSTR.

SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', 1, 1) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', 1, 2) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', 1, 3) str_pos
 FROM DUAL;

Parameter ‘n’ is optional and when not specified INSTR takes default as 1.

SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', 1) str_pos
 FROM DUAL;

Parameter ‘m’ and ‘n’ are not supplied to INSTR, Oracle raises error.

SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA') str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', 0, 1) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', NULL, 1) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', 1, NULL) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', -1, 1) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', -1, 2) str_pos
 FROM DUAL;
SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', -1, 3) str_pos
 FROM DUAL;

Parameter ‘n’ must be greater than 0, else Oracle raises error ORA-01428: argument ‘-n’ is out of range.

SELECT 'THE ULTIMATE CODEMAN' str,
 INSTR('THE ULTIMATE CODEMAN', 'MA', -1, -2) str_pos
 FROM DUAL;
 *
 ERROR at line 3:
 ORA-01428: argument '-2' is out of range

Q : Get the email provider and extension from the email ‘code-help.me@dcodeman.com’?

SELECT 'code-help.me@dcodeman.com' Actual,
 SUBSTR('code-help.me@dcodeman.com', INSTR('code-help.me@dcodeman.com', '@', 1, 1)+1, INSTR('code-help.me@dcodeman.com', '.', 1, 1)- INSTR('code-help.me@dcodeman.com', '@', 1, 1)-1) Company,
 SUBSTR('code-help.me@dcodeman.com', INSTR('code-help.me@dcodeman.com', '.', 1, 1)+1) Ext
 FROM DUAL;
Was this article helpful to you? Yes No

How can we help?