Oracle SUBSTR function.

Preamble

The Oracle/PLSQL SUBSTR function allows extracting a substring from a string.

Syntax of the Oracle/PLSQL SUBSTR function

SUBSTR( string_id, start_position_id, [ length_id ] ) 

Parameters and function arguments

• string_id – source line.
• start_position_id – position to start extraction of substring. The first position in the string is always equal to 1.
• length_id – is not mandatory. Defines the number of characters to be extracted. If this parameter is not specified, the SUBSTR function will return the entire string.

Note:

• If start_position is 0, the SUBSTR function will accept start_position as 1 (i.e.: first position in a string).
• If start_position is a positive number, the SUBSTR function starts reading from the beginning of the line.
• If start_position is a negative number, the SUBSTR function starts from the end of the line and counts in the opposite direction.
• If length is a negative number, the SUBSTR function returns NULL.

The SUBSTR function can be used in the following Oracle/PLSQL versions

Let’s consider some examples of SUBSTR function and learn how to use SUBSTR function in Oracle/PLSQL.

SELECT SUBSTR('This is a test', 6, 2) FROM DUAL;

--Result: is

SELECT SUBSTR('This is a test', 6) FROM DUAL;

--Result: is a test

SELECT SUBSTR ("HowZertIzTabakerky", 1, 3) FROM DUAL;

--Result: How

SELECT SUBSTR ("HowZertIzTabakerky", -15, 4) FROM DUAL;

--Result: Zert

SELECT SUBSTR ("HowZertIzTabakerky", -11, 2) FROM DUAL;

--Result: Iz

SELECT SUBSTR ("HowZertIzTabakerky", -9, 9) FROM DUAL;

--Result: Tabakerky

About Enteros

​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​