Rechercher le "bon" bloc dans la chaine

**thepierre** · 10/07/2012, 16h32

dans la même veine, voici une chaine qui me permet de dégager les blocs (variables) contigus de chiffres(uniques mais variables dans leur présence, entre 0 et 9, c'est la chaine str)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select str,
       regexp_substr(str, '((0,)|(1,)|(2,)|(3,)|(4,)|(5,)|(6,)|(7,)|(8,)|(9,))+',1,lvl)
  from (select '0,1,,3,4,5,,7,,,' str from dual) t, (select level lvl from dual connect by level <10)u;

Avec cette expression je trouve bien des blocs de chiffres contigus [0,1], [3,4,5], [7].

J'aimerais me passer du "select level from dual connect by level" et enrichir ma regexp pour y indiquer en plus que la chaine doit contenir "4" par exemple, ce qui correspondrait à un opérateur "and" fusionné aux opérateurs chainés "or" existants.

Donc en français, in french : "rechercher dans la chaine le bloc unique de chiffres contigus dans lequel est inclus un chiffre variable".

Par exemple quand 4 est passé, le bloc retourné doit être[3,4,5], et on est sûr qu'à chaque fois, une et une seule valeur existe dans la chaine (pas d'utilisation de paramètre "occurence" dans regexp_substr). ou éventuellement pas de valeur du tout.

Donc the question : comment exprimer ce "and" dans la regexp., car le faire figurer avant ou après la chaine de "or" existante induit une position...

a+

**mnitu** · 11/07/2012, 09h57

Pourriez-vous ajouter un petit jeu d’essaie avec divers cas et les résultats attendues ?
Que est-ce que vous gène vraiment dans la solution que vous avez trouvé ?

**thepierre** · 11/07/2012, 10h44

bjr Marius

Ce qui me gène c'est que si j'inclus ce bout de code dans une table à 3 millions de lignes, je vais multiplier ce volume par autant de blocs qu'il en existe dans la chaine(3 ou 4), c'est à dire 9 à 12 millions.

Et donc par exemple, avec une chaine de données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

'0,1,,3,4,5,,7,,,'

et un paramètre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

le select ne doit me fournir en retour qu'une et une seule ligne, avec le bloc comprenant le paramètre i.e.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

3,4,5

Cette solution au lieu de passer par un sous select qui retourne (le code du premier message):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

[0,1], [3,4,5], [7]

que l'on peut bien sûr filtrer ensuite mais qui va générer beaucoup trop de volume.

D'où mon besoin d'affiner la regexp pour "taper" directement sur le bon bloc. Peut-être ma formulation de regexp est-elle complètement à revoir.

N'hésites pas si tu ne comprends pas.

merci a+

**McM** · 11/07/2012, 10h52

Tu ne veux pas le coder sans regexp, avec juste des instr et substr ?

**mnitu** · 11/07/2012, 11h45

Pour ce type de problème multiplier les lignes n’est pas gênant, Oracle réponde assez bien (il est fait pour ça) et souvent ce type de solution dépasse de loin les autres.

D’autre part les expressions régulières sont plus gourmandes en ressources que les fonctions de chaine des caractères habituelles donc si vous avez une grosse volumétrie pensez à la proposition de McM

Fournissez aussi un jeu d’essai avec les résultats attendus.

**salim11** · 12/07/2012, 16h30

Salut,
Essaie ce code.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
WITH t AS
     (SELECT '0,1,,3,4,5,,7,,,' str
        FROM DUAL),
     tt AS
     (SELECT str, REGEXP_REPLACE (str||',', '[,]+', ',') chaine
        FROM t),
     r AS
     (SELECT str, chaine, LENGTH (REGEXP_REPLACE (chaine, '[^,]+')) cpt
        FROM tt),
     r1 AS
     (SELECT     str, LEVEL lvl,
                 REGEXP_SUBSTR (chaine, '[^,]+', 1, LEVEL) elem
            FROM r
      CONNECT BY LEVEL <= cpt),
     groupe AS
     (SELECT str, elem, elem - ROW_NUMBER () OVER (ORDER BY lvl) + 1 grp
        FROM r1),
     resultat AS
     (SELECT     str, grp,
                 LTRIM
                    (MAX (SYS_CONNECT_BY_PATH (elem, ','))KEEP (DENSE_RANK LAST ORDER BY curr),
                     ','
                    ) AS str_final
            FROM (SELECT str, grp, elem,
                         ROW_NUMBER () OVER (PARTITION BY grp ORDER BY str)
                                                                      AS curr,
                           ROW_NUMBER () OVER (PARTITION BY grp ORDER BY grp)
                         - 1 AS prev
                    FROM groupe)
        GROUP BY str, grp
      CONNECT BY prev = PRIOR curr AND grp = PRIOR grp
      START WITH curr = 1)
SELECT str, grp, str_final
  FROM resultat
 WHERE REGEXP_LIKE (str_final, :p) OR :p IS NULL;

Démo

Pour toutes les chaines

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
 
SET verify off
COLUMN str format a20
COLUMN str_final format a20
 
WITH t AS
     (SELECT '0,1,,3,4,5,,7,,,' str
        FROM DUAL),
     tt AS
     (SELECT str, REGEXP_REPLACE (str||',', '[,]+', ',') chaine
        FROM t),
     r AS
     (SELECT str, chaine, LENGTH (REGEXP_REPLACE (chaine, '[^,]+')) cpt
        FROM tt),
     r1 AS
     (SELECT     str, LEVEL lvl,
                 REGEXP_SUBSTR (chaine, '[^,]+', 1, LEVEL) elem
            FROM r
      CONNECT BY LEVEL <= cpt),
     groupe AS
     (SELECT str, elem, elem - ROW_NUMBER () OVER (ORDER BY lvl) + 1 grp
        FROM r1)
SELECT     str, grp,
           LTRIM
              (MAX (SYS_CONNECT_BY_PATH (elem, ','))KEEP (DENSE_RANK LAST ORDER BY curr),
               ','
              ) AS str_final
      FROM (SELECT str, grp, elem,
                   ROW_NUMBER () OVER (PARTITION BY grp ORDER BY str) AS curr,
                     ROW_NUMBER () OVER (PARTITION BY grp ORDER BY grp)
                   - 1 AS prev
              FROM groupe)
  GROUP BY str, grp
CONNECT BY prev = PRIOR curr AND grp = PRIOR grp
START WITH curr = 1
 
STR                         GRP STR_FINAL           
-------------------- ---------- --------------------
0,1,,3,4,5,,7,,,              0 0,1                 
0,1,,3,4,5,,7,,,              1 3,4,5               
0,1,,3,4,5,,7,,,              2 7                   
 
 
3 rows selected.

Pour une chaine bien précise exemple une série qui contient le numéro 4:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
set verify off
column str format a20
column str_final format a20
define p=4
 WITH t AS
     (SELECT '0,1,,3,4,5,,7,,,' str
        FROM DUAL),
     tt AS
     (SELECT str, REGEXP_REPLACE (str||',', '[,]+', ',') chaine
        FROM t),
     r AS
     (SELECT str, chaine, LENGTH (REGEXP_REPLACE (chaine, '[^,]+')) cpt
        FROM tt),
     r1 AS
     (SELECT     str, LEVEL lvl,
                 REGEXP_SUBSTR (chaine, '[^,]+', 1, LEVEL) elem
            FROM r
      CONNECT BY LEVEL <= cpt),
     groupe AS
     (SELECT str, elem, elem - ROW_NUMBER () OVER (ORDER BY lvl) + 1 grp
        FROM r1),
     resultat AS
     (SELECT     str, grp,
                 LTRIM
                    (MAX (SYS_CONNECT_BY_PATH (elem, ','))KEEP (DENSE_RANK LAST ORDER BY curr),
                     ','
                    ) AS str_final
            FROM (SELECT str, grp, elem,
                         ROW_NUMBER () OVER (PARTITION BY grp ORDER BY str)
                                                                      AS curr,
                           ROW_NUMBER () OVER (PARTITION BY grp ORDER BY grp)
                         - 1 AS prev
                    FROM groupe)
        GROUP BY str, grp
      CONNECT BY prev = PRIOR curr AND grp = PRIOR grp
      START WITH curr = 1)
SELECT str, grp, str_final
  FROM resultat
 WHERE REGEXP_LIKE (str_final, &p) OR &p IS NULL;

STR                         GRP STR_FINAL           
-------------------- ---------- --------------------
0,1,,3,4,5,,7,,,              1 3,4,5               


1 row selected.

==========================================

avec DEFINE p=1

STR                         GRP STR_FINAL           
-------------------- ---------- --------------------
0,1,,3,4,5,,7,,,              0 0,1                 


1 row selected.

avec DEFINE p=7

STR                         GRP STR_FINAL           
-------------------- ---------- --------------------
0,1,,3,4,5,,7,,,              2 7                   


1 row selected.

Rechercher le "bon" bloc dans la chaine

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager