How do I Substring a String in Oracle?

How do I Substring a String in Oracle?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

To substring a string you can use the substr function in Oracle. The SUBSTR functions return a substring of string, starting at character position and lasting substring length characters. The following are the substr function syntax and some examples:

Substr Function Syntax

substr(char, position, substring_length);

The SUBSTR functions return a substring of char, starting at character position and lasting substring length characters.

Substring a String in Oracle Examples

The following SQL query will substring the string SQL from the string 'Oracle SQL':

select substr('Oracle SQL', 8,3) from dual;

Output:

SQL

To get the last three characters of a string, you can use the substr() function as below:

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

Output:

SQL

How to Use SUBSTR function in PL/SQL?

Below is an example of using the substr function in PL/SQL code:

Declare
   v_string varchar2(100);
Begin
   v_string := substr('this is a string.', 11);
   dbms_output.put_Line(v_string);
End;

Output:

string

See also: