oracle split varchar

Version imprimable

Bonjour,

Version oracle:

Code:

1
2
3
4
5
6
 
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE	10.2.0.3.0	Production
TNS FOR IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

J'ai une table du style

Code:

1
2
3
4
5
6
 
champ1 | champ 2
l1          |   x1,x2,x3
l2          |   x3,x4
l5          |   x5
l5          |   x1,x4,x6,x7

et je souhaiterais obtenir une vue

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
champ1 | champ 2
l1          |   x1
l1          |   x2
l1          |   x3
l2          |   x3
l2          |   x4
l5          |   x5
l5          |   x1
l5          |   x4
l5          |   x6
l5          |   x7

J'imagine qu'il faut utiliser une function de split ..

D'avance merci pour votre aide

23/06/2011, 17h06
mnitu

String to Rows

Bonjour,

Je pense qu'il te faut utilser

Code:

REGEXP_SUBSTR

Voici un lien traitant de ce sujet :
http://forums.oracle.com/forums/thre...hreadID=958898

ça doit donner un truc du genre, pas pu tester comme en ce moment je taf chez un client en 9i donc à confirmer :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH TEST
AS
(
SELECT 'l1' AS ID    , 'x1,x2,x3' AS value   from dual UNION ALL
SELECT 'l2'          , 'x3,x4'               from dual UNION ALL
SELECT 'l1'          , 'x5'                  from dual UNION ALL
SELECT 'l1'          , 'x1,x4,x6,x7'         from dual 
)
    SELECT ID , REGEXP_SUBSTR (value , '[^,]+', 1, LEVEL) AS result    
    FROM   TEST    
    CONNECT BY LEVEL <= ( SELECT LENGTH (REPLACE (value , ',', NULL)) 
                                      FROM TEST)
;

Cordialement.

24/06/2011, 12h46
pacmann

Salut !

Ca marche pas pour la bonne raison que la gruge du CONNECT BY level, tu ne peux l'utiliser que pour dupliquer une seule ligne.
A partir du moment où ta sélection initiale fait plus qu'une ligne, c'est mort.

En plus, ce que tu as mis derrière le CONNECT BY level fait planter la requête (c'est un SELECT non corrélé : ça renvoie pleins de lignes et non pas un seul nombre)

Une autre méthode se rapprochant de Integer Series Generator Method dans le lien proposé par mnitu mais ne nécessitant aucune création de TYPE :

Code:

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
31
32
33
34
35
36
SQL> with t as (
select 'l1' as c1, 'x1,x2,x3' as c2 from dual union all
select 'l2'      , 'x3,x4'          from dual union all
select 'l5'      , 'x5'             from dual union all
select 'l5'      , 'x1,x4,x6,x7'    from dual
)
select t.c1, x.column_value as list_c2
  from t
 cross join table(
              cast(multiset(
                       select substr( ','||c2||',',
                                      instr( ','||c2||',', ',', 1, rownum )+1,
                                      instr( ','||c2||',', ',', 1, rownum+1 )
                                             -instr( ','||c2||',', ',', 1, rownum )-1
                                     )
                         from dual
                      connect by level <= length(c2)-length(replace(c2,',',''))+1
                            ) as sys.odcivarchar2list )
                    ) x;
 
C1 LIST_
-- -----
l1 x1
l1 x2
l1 x3
l2 x3
l2 x4
l5 x5
l5 x1
l5 x4
l5 x6
l5 x7
 
10 rows selected.
 
SQL>

Merci Tom Kyte pour le bon timing du post :P (et pour tout le reste évidemment) :
sorting by number