CODEWITHSUNDEEP
sqloracle-databaseselectoracle11g
simplify_life
simplify_life

Reputation: 405

oracle sql split string to rows

I have string 'ABC' I need to split into rows as below

A
B
C

.I know how do do when delimiter is present. How about when delimiter is not present

with test as
(select 'A,B,C' col1 from dual)
  select regexp_substr(col1, '[^,]+', 1, rownum) result1
  from test
  connect by level <= length(regexp_replace(col1, '[^,]+')) + 1;

Upvotes: 1

Views: 603

Answers (2)

P.Ilya
P.Ilya

Reputation: 1

You can use a function like this

-- define type
CREATE OR REPLACE TYPE TABLE_OF_STRING AS TABLE OF VARCHAR2(32767);

-- function
function SPLIT_STRING_TO_STRINGS
  (
    p_list varchar2,
    p_delimiter varchar2 := ','
  ) return TABLE_OF_STRING pipelined
  is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
  begin
    loop
        l_idx := instr(l_list, p_delimiter);
        if l_idx > 0 then
            pipe row(substr(l_list, 1, l_idx-1));
            l_list := substr(l_list, l_idx + length(p_delimiter));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
  end SPLIT_STRING_TO_STRINGS;

-- usage example
select * from table(SPLIT_STRING_TO_STRINGS('A,B,C',','))

Upvotes: 0

Mureinik
Mureinik

Reputation: 311143

Without a delimiter it should be even easier - use the same approach, but just use substr with level as the index of the string:

with test as
(select 'ABC' col1 from dual)
  select substr(col1, level, 1) result1
  from test
  connect by level <= length(col1);

Upvotes: 4

Related Questions