Convert Varchar2 to Char array in Oracle

oracle split char varchar2
MH2538 picture MH2538 · Oct 1, 2013 · Viewed 9.6k times · Source

I have a varchar2 field and want to split it to array of chars Like 'ABCDEF' --> 'A' 'B' 'C' 'D' 'E' How can i convert my Field Values to chars array?

Answer

Justin Cave picture Justin Cave · Oct 1, 2013

If you actually mean a PL/SQL collection of characters, you could do something like

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    type char_arr is table of char(1) index by pls_integer;
  3    l_str varchar2(100) := 'ABCDEF';
  4    l_arr char_arr;
  5  begin
  6    for i in 1 .. length(l_str)
  7    loop
  8      l_arr(i) := substr( l_str, i, 1 );
  9    end loop;
 10    dbms_output.put_line( l_arr.count );
 11* end;
SQL> /
6

PL/SQL procedure successfully completed.

Without understanding the business requirements, though, I would tend to be very suspicious. When you find yourself breaking apart strings in PL/SQL, that almost always implies that you have stored data in a non-atomic form and need to address the data model issue.

Related questions

Split String by delimiter position using oracle SQL

I have a string and I would like to split that string by delimiter at a certain position. For example, my String is F/P/O and the result I am looking for is: Therefore, I would like to separate …

Read More
Is there a function to split a string in PL/SQL?

I need to write a procedure to normalize a record that have multiple tokens concatenated by one char. I need to obtain these tokens splitting the string and insert each one as a new record in a table. Does Oracle …

Read More
Split function in oracle to comma separated values with automatic sequence

Need Split function which will take two parameters, string to split and delimiter to split the string and return a table with columns Id and Data.And how to call Split function which will return a table with columns Id …

Read More