Convert Varchar2 to Char array in Oracle

Question

I have a varchar2 field and you want to split it into a character array Like "ABCDEF" → 'A' 'B' 'C' 'D' 'E' How can I convert the field values ​​to a character array?

Answer 1

If you really mean a collection of PL / SQL characters, you can 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.

Not understanding the business requirements, I would most likely be very suspicious. When you find that you are breaking lines in PL / SQL, this almost always means that you saved the data in a non-atomic form and should solve the problem with the data model.