Search code examples
sqloracle-databasedatastage

how to extract variable lengh of strings from file in datasatage using substring or index function

Input : 

start1
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
start2

I want to capture whatever string is in between start1 and start2. String between them would be variable. Output: 

david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com

Using Substring/Index function

Solution

  • if its in pl/sql then you can do this:

    SQL> declare
  2    v_str varchar2(2000) := 'start1
  3  david@gmail.com
  4  david@gmail.com
  5  david@gmail.com
  6  david@gmail.com
  7  david@gmail.com
  8  david@gmail.com
  9  david@gmail.com
 10  start2';
 11    v_newstr varchar2(2000);
 12    v_start_delim varchar2(10) := 'start1';
 13    v_end_delim varchar2(10) := 'start2';
 14  begin
 15    v_newstr := substr(v_str, instr(v_str, v_start_delim)+length(v_start_delim),
 16                       instr(v_str, v_end_delim) - instr(v_str, v_start_delim) - length(v_start_delim));
 17
 18    -- remove leading/trailing breaks.
 19    v_newstr := trim(both chr(10) from v_newstr);
 20    dbms_output.put_line(v_newstr);
 21
 22  end;
 23  /
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com

PL/SQL procedure successfully completed.

    or if you had the string in SQL (assuming the string is all in one row)

    SQL> select trim(both chr(10) from
 2           substr(str, instr(str, 'start1')+length('start1'),
 3                instr(str, 'start2') - instr(str, 'start1') - length('start1'))
 4         ) newstr
 5    from data;

NEWSTR
-----------------------
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com
david@gmail.com