PL/SQL Substr

The Oracle PL/SQL Substr function is used to extract a substring from a string. The syntax of the function is:

Substr syntax

SUBSTR (source_string , start_position, substring_length)

where
– source_string is the original string
– start_position is the position in the original string where the substring starts
– length is the number of characters in the substring.

If you omit the length parameter, then Oracle Substr function will return all characters from the start_position to the end of the source string.

Substr example

The following example extracts a substring of 3 characters starting at position 7 within the string ‘My pl/sql substring’:

 
select substr('My pl/sql substring', 7, 3) from dual;

Result: 'sql'

You can also use the SUBSTR function to return the entire string, starting from a specific position. For example, to return the entire string starting from the 7th character within the string ‘My pl/sql substring’:

select substr('My pl/sql substring', 7) from dual;

Result: 'sql substring'

The following example extracts a substring of 9 characters starting at position 1 within the string ‘My pl/sql substring’:

select substr('My pl/sql substring', 1, 9) from dual;

Result: 'My pl/sql' 

If you need to extract a substring from the beginning of a string, you can use the SUBSTR function with a negative value for the start_position parameter. For example, to extract the first 3 characters from the string ‘Learn SQL’:

select substr('Learn SQL', -3) from dual;

Result: 'Lea'

If you need to find all employees whose last name contains ‘S’ anywhere within the string, you can use the SUBSTR function. For example:

select * from employees where substr(last_name, 1, 1) like '%S%'; 

Substr example

The example below shows how to extract the first 3 characters of the city.

select s.STUDENT_ID, s.FIRST_NAME, s.LAST_NAME, 
s.CITY, substr(s.CITY, 1,3)  AS SHORT_CITY
from students s;

STUDENT_ID	FIRST_NAME	LAST_NAME	CITY	SHORT_CITY
1	Daniel	SCOTT	New York	New
2	Anthony	SIMMONS	Chicago	Chi
3	Sophia	THOMPSON	Los Angeles	Los