Returns specified characters from character value, starting from a specified postion 'm' to 'n' characters Long.

Syntax :

 SUBSTR( Col/ Expr,m,n)

Points to remember :

• If "m" is 0, it is treated as 1.
• If "m" is positive, oracle counts from the beginning of string to find the first character.
• If "m" is negative, oracle counts backwards from the end of string.
• If "m" is omitted, oracle returns all characters to the end of string.
• If "n" is less than 1 or 0, a NULL is returned.
• Floating point numbers passed as arguments to SUBSTR are automatically converted to intergers.

Examples:

1.
SELECT 'ABCDEFGH' String,
SUBSTR( 'ABCDEFGH' ,3,4) Substring
FROM DUAL;

2.
SELECT 'ABCDEFGH' String,
SUBSTR( 'ABCDEFGH' ,-5,4) Substring
FROM DUAL;

3.
SELECT 'ABCDEFGH' String,
SUBSTR( 'ABCDEFGH' ,0,4) Substring
FROM DUAL; 

4.
SELECT 'ABCDEFGH' String,
SUBSTR( 'ABCDEFGH' ,4) Substring
FROM DUAL; 

       
5.
SELECT 'ABCDEFGH' String,
SUBSTR( 'ABCDEFGH' ,4,0) Substring
FROM DUAL;

5.
SELECT 'ABCDEFGH' String,
SUBSTR( 'ABCDEFGH' ,4,-2) Substring
FROM DUAL;

6.
SELECT Ename, Job
FROM Emp
WHERE SUBSTR(Job,4,3) = UPPER('age');