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;
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);
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',','))
