-1

How to split String by new line or other char String 

ABC|123
xyz|098

To 

ABC --- 123
xyz --- 098
asraful009
  • 585
  • 1
  • 6
  • 11
  • Your requirement is not very clear. So `ABC --- 123` is a single string output? Why not just use `select replace(column,'|',' --- ') from your table`? – Utsav May 01 '17 at 17:23
  • @Utsav There is no requirement here , that's why I mark it down. – Seyran May 01 '17 at 17:26
  • This is unclear. You seem to show two columns, not just a single string value. Do you need to split BOTH, and perhaps in a coordinated way? What if the values in the two columns (in the same original rows) don't have the same number of newline characters? Do you need to keep track of which line was first and which was second? - I offer this simply as an illustration of the fact that you must add a lot more details to your request. Until you do so, I'll recommend closing as unclear. –  May 01 '17 at 17:49
  • Are you are looking for the `REPLACE()` function maybe? – Tenzin May 01 '17 at 19:07

2 Answers2

3

Try using regexp_substr function...

select regexp_substr('ABC|123','[^|]+',1,1) part1, regexp_substr('ABC|123','[^|]+',1,2) part2 from dual

And in case of new line (CR/LF) it would look like this:

   `regexp_substr(mystr,'[^('||chr(13)||chr(10)||')]+',1,1) part1,
    regexp_substr(mystr,'[^('||chr(13)||chr(10)||')]+',1,2) part2`
PKey
  • 3,715
  • 1
  • 14
  • 39
  • @asraful009 what do you mean 'does not work'? Your problem description gives too little detail (hence your negative score). It is not clear what are you trying to achieve exactly... for example `select trim(regexp_substr(mystr,'[^('||chr(13)||chr(10)||')]+',1,level)) parts from dual connect by level <100` will break your string into 99 rows (does it work for you?) , or if you loop through `regexp_substr(mystr,'[^('||chr(13)||chr(10)||')]+',1,i)`, you can get whatever `i` part of the string (doesn't matter if it is more than 100 or not). So, please refine your question - give more examples – PKey May 02 '17 at 05:07
1

Make oracle split function, that return varray. Here V_ARRAY is type

  1. create TYPE V_ARRAY
  2. after that create split function which return V_ARRAY
CREATE OR REPLACE TYPE V_ARRAY AS VARRAY(1000) OF VARCHAR2(4000);

CREATE or REPLACE function split(str varchar2, patt varchar2)
RETURN v_array as
  s BINARY_integer;
  e BINARY_integer;
  i BINARY_integer;
  token v_array;
begin
  token := v_array();
  i :=1;
  s := 0;
  e := INSTR(str, patt);
  while(e > 0) loop
      token.extend; // add new varchar2
      token(i) := substr(str, s+1, e-s-1);
      s := e;
      e := INSTR(str, patt, s+1);
      i := i + 1;
  end loop;
  token.extend;
  token(i) := substr(str, s+1, length(str) -1);
  return token;
end;

Example

declare
  str  varchar(10000);
  line varchar(10000);
  lines v_array;
  subs v_array;
begin
  str := 'ABCe123';--|| chr(10) ||'xyz|098';
  lines := split(str, chr(10));
  --DBMS_OUTPUT.PUT_LINE(lines.count);
  for i in 1.. lines.count loop
    line := lines(i);  
   -- DBMS_OUTPUT.PUT_LINE( 'line :' ||  line);
    subs := split(line, '|');
   -- DBMS_OUTPUT.PUT_LINE(subs.count);
    DBMS_OUTPUT.PUT_LINE( subs(1) || '---' || subs(1) );
  end loop;      
end;

ref:A function to split, loop through

Community
  • 1
  • 1
asraful009
  • 585
  • 1
  • 6
  • 11