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;