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;