1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| SQL> var txt varchar2(4000);
SQL> exec :txt := '300030120000020000010,300030120000020000020,300030120000020000030';
PL/SQL procedure successfully completed.
SQL> with data as (
2 select level as lvl,
3 trim( substr (txt,
4 instr (txt, ',', 1, level ) + 1,
5 instr (txt, ',', 1, level+1)
6 - instr (txt, ',', 1, level) -1 )
7 ) as token
8 from (select ','||:txt||',' as txt
9 from dual)
10 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
11 ),
12 t as (
13 select lvl, token, row_number() over (order by lvl) as rn
14 from data
15 where token not in (select nucp from tbcp)
16 )
17 select token
18 from t
19 where rn = 1;
TOKEN
------------------------------
300030120000020000010
SQL> |
Partager