Home » SQL & PL/SQL » SQL & PL/SQL » opposite of listagg (Oracle 11g)
| opposite of listagg [message #569451] |
Fri, 26 October 2012 07:25  |
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 #569455 is a reply to message #569453] |
Fri, 26 October 2012 07:57  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
muralikri wrote on Fri, 26 October 2012 14:48Just 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 #644993 is a reply to message #569451] |
Mon, 23 November 2015 05:19 |
 |
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 #645001 is a reply to message #569451] |
Mon, 23 November 2015 07:23 |
|
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Fri Oct 31 04:57:52 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
