Using Substr with Instr to Extract 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.
  • PLSQL / SQL
  • 1 min read

In Oracle, use substr function in combination with instr function to extract a string from a string. Below are the examples.

Substr Function with Instr Function Examples

1. Extract a string after a specified character

Below example will extract the rest of a string after the $ sign.

set serveroutput on;
declare
v_string varchar2(20) := 'USD$500.67';
v_string1 varchar2(20);
begin
v_string1 := substr(v_string, instr(v_string, '$') +1);
dbms_output.put_Line(v_string1);
end;
/

Output

500.67
PL/SQL procedure successfully completed.

2. Extract a string after a specified character to another specified character

This example will extract from the first specified string "$" to the second specified string ".".

set serveroutput on;
DECLARE
v_string VARCHAR2 (20) := 'USD$500.67';
v_string1 VARCHAR2 (20);
BEGIN
v_string1 :=
SUBSTR (v_string,
INSTR (v_string, '$'),
INSTR (v_string, '.') - INSTR (v_string, '$'));
DBMS_OUTPUT.put_Line (v_string1);
END;
/

Output

$500
PL/SQL procedure successfully completed.

See also: