Home » SQL & PL/SQL » SQL & PL/SQL » opposite of listagg (Oracle 11g)
opposite of listagg [message #569451] Fri, 26 October 2012 07:25 Go to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I got some of numbers in the one column as a string separated by ;
and I would like to get every of this number into the row.

For instance:
COLUMN
421907802490;421907672085;421911460415;421905464170;421907802292

And result should be:
COLUMN
421907802490
421907672085
421911460415
421905464170
421907802292


Does anybody know how to do that?

Thanks a lot

Regards
Re: opposite of listagg [message #569452 is a reply to message #569451] Fri, 26 October 2012 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can use the varying in list method
Re: opposite of listagg [message #569453 is a reply to message #569452] Fri, 26 October 2012 07:48 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Just Try like...

with test as
      (select '421907802490;421907672085;421911460415;421905464170;421907802292' col from dual)
    select regexp_substr(col, '[^;]+', 1, level) result
    from test
    connect by level <= length(regexp_replace(col, '[^;]+')) + 1; 
Re: opposite of listagg [message #569454 is a reply to message #569453] Fri, 26 October 2012 07:53 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Thats work perfect by using regexp_substr.

thanks
Re: opposite of listagg [message #569455 is a reply to message #569453] Fri, 26 October 2012 07:57 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
muralikri wrote on Fri, 26 October 2012 14:48
Just Try like...

with test as
      (select '421907802490;421907672085;421911460415;421905464170;421907802292' col from dual)
    select regexp_substr(col, '[^;]+', 1, level) result
    from test
    connect by level <= length(regexp_replace(col, '[^;]+')) + 1; 

Your solution will not work if there are several ';' delimited rows.



A more general solution would be this

WITH tmptab AS
(
    SELECT '421907802490;421907672085;421911460415;421905464170;421907802292'
    AS colval FROM DUAL 
    UNION
    SELECT '111111111;222222222;333333333333;4444;55555555555' FROM DUAL
)
SELECT  REGEXP_SUBSTR(t1.colval, '([^;])+', 1, t2.COLUMN_VALUE)
FROM tmptab t1 CROSS JOIN
            TABLE
            (
                CAST
                (
                    MULTISET
                    (
                        SELECT LEVEL
                        FROM DUAL 
                        CONNECT BY LEVEL <= REGEXP_COUNT(t1.colval, '([^;])+')
                    )
                    AS SYS.odciNumberList
                )
            ) t2;


REGEXP_SUBSTR(T1.COLVAL,'([^;])+',1,T2.COLUMN_VALUE)
----------------------------------------------------------------
111111111
222222222
333333333333
4444
55555555555
421907802490
421907672085
421911460415
421905464170
421907802292

10 rows selected.

SQL> 




Regards,
Dariyoosh

[Updated on: Fri, 26 October 2012 08:00]

Report message to a moderator

Re: opposite of listagg [message #569461 is a reply to message #569455] Fri, 26 October 2012 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Your solution will not work if there are several ';' delimited rows.


He DOES know it as I already told and explained him so.
Too bad, he is unable to learn and fix his answer.
Thanks for correcting his answer.

Regards
Michel
Re: opposite of listagg [message #644993 is a reply to message #569451] Mon, 23 November 2015 05:19 Go to previous messageGo to next message
Antony_vilson
Messages: 3
Registered: November 2015
Junior Member
An anonymous block.....

declare
listt varchar2(99) := ';421907802490;421907672085;421911460415;421905464170;421907802292;';
begin
for i in 1 .. regexp_count(listt, ';') - 1 loop
dbms_output.put_line(substr(listt,
instr(listt, ';', 1, i) + 1,
(instr(listt, ';', 1, i + 1) -
instr(listt, ';', 1, i)) - 1));
end loop;
end;

Re: opposite of listagg [message #644996 is a reply to message #644993] Mon, 23 November 2015 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Er... you answer a 3 years old topic which already has a solution with a weaker one. /forum/fa/1600/0/

Re: opposite of listagg [message #644997 is a reply to message #644996] Mon, 23 November 2015 06:25 Go to previous messageGo to next message
Antony_vilson
Messages: 3
Registered: November 2015
Junior Member
I meant it for future references,If any people need an anonymous block...
Re: opposite of listagg [message #644999 is a reply to message #644997] Mon, 23 November 2015 06:59 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
You mean if any people need to use a significantly poorer solution than was proposed 3 years ago? Hmm, okay then.
Re: opposite of listagg [message #645001 is a reply to message #569451] Mon, 23 November 2015 07:23 Go to previous messageGo to next message
Antony_vilson
Messages: 3
Registered: November 2015
Junior Member


SELECT
substr(VAL,instr(val,';',1,level)+1,(instr(val,';',1,level + 1) - instr(val,';',1,level))-1)
from DEP connect by level <= regexp_count(VAL,';') - 1

where val is ;421907802490;421907672085;421911460415;421905464170;421907802292;
Re: opposite of listagg [message #645003 is a reply to message #645001] Mon, 23 November 2015 07:53 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Antony_vilson wrote on Mon, 23 November 2015 13:23


SELECT
substr(VAL,instr(val,';',1,level)+1,(instr(val,';',1,level + 1) - instr(val,';',1,level))-1)
from DEP connect by level <= regexp_count(VAL,';') - 1

where val is ;421907802490;421907672085;421911460415;421905464170;421907802292;


Nope,
works ok with a single value input, but what about:
with t as
        (select ' ;421907802490;421907672085;421911460415;421905464170;421907802292;' val
        from    dual
        union all
        select ' ;42190783490;42190767285;421911460415;4219054643170;4215802292;' val
        from    dual
        )
select  substr(val,instr(val,';',1,level)+1,(instr(val,';',1,level + 1) - instr(val,';',1,level))-1
        )
from    t
        connect by level <= regexp_count(val,';') - 1;


(This has already been pointed out in this very thread, and resolved... in this very thread.
Previous Topic: WITH clause working in 10g but not in 11g (XE)
Next Topic: MERGE: DELETE CLAUSE definition explanation.
Goto Forum:
  


Current Time: Mon Mar 18 23:11:20 CDT 2024