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:
- Count Number of Characters in a String in Oracle
- How to Fetch data from Cursor using For Loop
- How to Execute PL/SQL block in Oracle