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: 232
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: 11208
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: 232
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: 536
Registered: March 2009
Location: Iran / 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 message
Michel Cadot
Messages: 59822
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
Previous Topic: how to get unique value in column ?
Next Topic: Materialized views
Goto Forum:
  


Current Time: Sat Nov 29 01:58:40 CST 2014

Total time taken to generate the page: 0.10750 seconds