Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 3 Oracle Functions
  5. Character Functions: SUBSTR, INSTR

Character Functions: SUBSTR, INSTR

SUBSTR function in Oracle

SUBSTR function returns a substring of the argument String, beginning from the ‘m’ character in the input string and ending with the number of  ‘n’ character.

Syntax: SUBSTR(String, m, n)

SELECT 
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 5, 10) SubString
  FROM DUAL;
SELECT
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 5, 3) SubString
  FROM DUAL;

You can specify arithmetical operation for the parameters ‘m’ and ‘n’. Oracle evaluates the values and takes the absolute value to decide ‘m’ and ‘n’.

SELECT 
    'TheUlimateCodeMan' Str,
    SUBSTR('TheUlimateCodeMan', 1.9+3.5, 7.5-1) Substr1,
    SUBSTR('TheUlimateCodeMan', 5.3+2.4, 5) Substr2,
    SUBSTR('TheUlimateCodeMan', 14.6) Substr3
  FROM DUAL;

1st and 2nd parameters to function SUBSTR are mandatory and 3rd parameter is optional. If you miss 2nd parameter ‘m’ then Oracle raises error ORA-00938: not enough arguments for function.

SELECT SUBSTR('The Code Man') FROM DUAL;

The last parameter ‘n’ to SUBSTR function is optional. When the last parameter is not supplied, SUBSTR return the substring starting from ‘m’ character.

SELECT 
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 5) SubString
  FROM DUAL;

When ‘n’ 3rd parameter is supplied but is NULL, then Oracle SUBSTR returns NULL.

SELECT 
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 5, NULL) SubString
  FROM DUAL;

When the 2nd parameter is NULL, Oracle SUBSTR returns NULL.

SELECT 
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', NULL, 5) SubString
 FROM DUAL;

When ‘m’ is negative, Oracle starts from the backward of the string, that means, from right to left. But parameter ‘n’ is always calculated from left to right.

SELECT
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', -10, 5) SubString
FROM DUAL;

When ‘n’ is negative, Oracle SUBSTR returns NULL.

SELECT 
    'The ultimate CodeMan'
    SUBSTR('The ultimate CodeMan', -10, -5) SubString
  FROM DUAL;

Check out some combination of ‘m’ and ‘n’ with SUBSTR.

SELECT
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 1, 0) SubString 
  FROM DUAL;
SELECT 
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 0, 1) SubString
  FROM DUAL;
SELECT
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', -1, 0) SubString
  FROM DUAL;
SELECT
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', 1, -1) SubString 
  FROM DUAL;
SELECT
    'The ultimate CodeMan' Name,
    SUBSTR('The ultimate CodeMan', -1, -1) SubString
  FROM DUAL;

INSTR function in Oracle

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?