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> column valeur format a30
SQL> with data as (
2 select '01' as id, '012345 FRAISE' as valeur from dual union all
3 select '02' , '012345 ABRICOT' from dual union all
4 select '03' , '012345 POMME,012345 MANGUE,012345 TOMATE' from dual
5 )
6 SELECT t.id, trim(translate(x.column_value,'0123456789',' ')) AS valeur
7 FROM data t
8 CROSS JOIN TABLE(
9 cast(multiset(
10 SELECT substr( ','||valeur||',',
11 instr( ','||valeur||',', ',', 1, rownum )+1,
12 instr( ','||valeur||',', ',', 1, rownum+1 )
13 -instr( ','||valeur||',', ',', 1, rownum )-1
14 )
15 FROM dual
16 connect BY level <= length(valeur)-length(REPLACE(valeur,',',''))+1
17 ) AS sys.odcivarchar2list )
18 ) x
19 /
ID VALEUR
-- ------------------------------
01 FRAISE
02 ABRICOT
03 POMME
03 MANGUE
03 TOMATE
SQL> |
Partager