Oracle SQL- special characters delimited strings

oracle-12c

I have a following string in my table in the following format:

APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED – 25%; GRAPES- GREEN – 20%

I want a output as:

Output

APPLE – 65%

Which is the sum of %quantity purchased of total.

Best Answer

select Col1, sum(Col3)  
from  
    ( 
    select  
        regexp_substr(column_value, '\w+', 1, 1) as Col1, 
        regexp_substr(column_value, '\w+', 1, 2) as Col2, 
        regexp_substr(column_value, '\w+', 1, 3) as Col3 
    from split('APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED - 25%; GRAPES- GREEN - 20%',';') 
    )  
group by Col1

enter image description here

create or replace type split_tbl as table of varchar2(32767); 
/

create or replace function split    
(    
    p_list varchar2,    
    p_del varchar2 := ','    
) return split_tbl pipelined    
is    
    l_idx    pls_integer;    
    l_list    varchar2(32767) := p_list;    
    l_value    varchar2(32767);    
begin 
    l_list := replace( l_list, ' ',  '' ); 
    loop    
        l_idx := instr(l_list,p_del);    
        if l_idx > 0 then    
            pipe row( substr( l_list, 1, l_idx-1) );    
            l_list := substr( l_list, l_idx + length(p_del) ); 
    
        else    
            pipe row(l_list);    
            exit;    
        end if;    
    end loop;    
    return;    
end split;
Related Question