Oracle SUBSTR function

Learn how to find a sub string from the main string using oracle SUBSTR function

The SUBSTR() function returns a substring from the str starting at start_position with the substring_length length.

Below is the syntax of sub-string.

Syntax

SUBSTR( str, start_position [, substring_length, [, occurrence ]] );

SUBSTR() function accepts three arguments:

str- string that you want to extract the substring
start_position- an integer that determines where the substring starts.
substring_length- determines the number of characters in the substring.

Notes:

1. If the start_position is 0, the begin of the sub-string will be at the first character of the str.
2. IF start_position is positive, the SUBSTR() function will count from the beginning of the str to determine the first character of the sub-string.
3. If the start_position is negative, then the SUBSTR() function will count backward from the end of the str to find the first character of the sub-string.
4. If substring_length is omitted, the SUBSTR() function returns all characters starting from the start_position.
5. If the substring_length is less than 1, the SUBSTR() function returns null.

Query Examples:

SELECT SUBSTR('VISWANATH.ANNANGI', 0, 9) SUBSTRING 
FROM DUAL;

--Substring from index 0 to 9 

Output

VISWANATH

SELECT SUBSTR('VISWANATH.ANNANGI', 0) SUBSTRING
FROM DUAL;
--Substring from index 0 to n character.  here n is the length of the string

Output

VISWANATH.ANNANGI

SELECT SUBSTR('VISWANATH.ANNANGI', -1) SUBSTRING 
FROM DUAL;

--Substring from the last index value

Output

I

SELECT SUBSTR('VISWANATH.ANNANGI', -9, 1) SUBSTRING 
FROM DUAL;
-- substring 9th character from  right to left side  

Output

H

SELECT SUBSTR('VISWANATH.ANNANGI', 8) SUBSTRING 
FROM DUAL;

--substring - 8 th charcter from left side to end of the string

Output

TH.ANNANGI

SELECT SUBSTR('VISWANATH.ANNANGI', -9, -1) SUBSTRING 
FROM DUAL;

--return null value if substring_length is  negative

Output

null

Query to find the fist_name , last_name, email_server, domain from the given input string.

Input string:

[email protected]

Query Example

WITH CTE AS
 (SELECT '[email protected]' AS EMAIL_ID 
  FROM DUAL
 )
SELECT SUBSTR(EMAIL_ID,1,INSTR(EMAIL_ID, '.', 1, 1)-1) FIRST_NAME,/*//0 to index of fist dot -1*/
       SUBSTR(EMAIL_ID,INSTR(EMAIL_ID, '.', 1, 1)+1,INSTR(EMAIL_ID, '@')-INSTR(EMAIL_ID, '.', 1, 1)-1) LAST_NAME,/*//index of fist dot +1 to index of @ -1*/
       SUBSTR(EMAIL_ID,INSTR(EMAIL_ID, '@', 1, 1)+1,INSTR(EMAIL_ID, '.',-1,1)-INSTR(EMAIL_ID, '@', 1, 1)-1) MAIL_SERVER,/*//index of @ +1 to index of last dot -1*/
       SUBSTR(EMAIL_ID,INSTR(EMAIL_ID, '.', -1, 1)+1) DOMAIN/* //index of last dot to end of the input string*/
       
FROM CTE;