manipulations de chaines de caractères sur Oracle

**ajor** · 30/07/2009, 16h03

Bonjour j'ai dans une de mes tables (Oracle) des enregistrements que j'aimerais modifier par les fonction instr, replace, etc. Mais c'est un peu compliqué, j'explique :

Mes rengistrements ressemblent à ça:

Code :

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

1
2
3
95% Rouge, 5% Vert
100% Noir Bleuté
50% Vert Clair, 45% Vert Foncé, 5% Vert Bizarre

Mon but est d'obtenir ça:

Code :

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

1
2
3
95% <a href="lien_rouge.htm">Rouge</a>, 5% <a href="lien_vert.htm">Vert</a>
100% <a href="lien_noir_bleute.htm">Noir Bleuté</a>
50% <a href="lien_vert_clair.htm">Vert Clair</a>, 45% <a href="lien_vert_fonce.htm">Vert Foncé</a>, 5% <a href="lien_vert_bizarre.htm">Vert Bizarre</a>

Je suppose qu'il y a une possibilité de requête pour réaliser cette modification puisqu'il y a une logique:
-les début des liens html sont situés toujours après "% "
-les fins des liens html sont situés toujours avant une virgule ou après le dernier caractère.
-les adresse des liens sont le mot en mininuscule, sans les accents, et les espaces sont remplacés par "_"

En me promenant sur ce forum, j'a vu que certains étaient très forts en manipulation de chaines de caractère,
que pensez-vous de cette requête ?

**ajor** · 30/07/2009, 16h13

Bonjour j'ai dans une de mes tables (Oracle) des enregistrements que j'aimerais modifier par les fonction instr, replace, etc. Mais c'est un peu compliqué, j'explique :

Mes rengistrements ressemblent à ça:

Code :

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

1
2
3
95% Rouge, 5% Vert
100% Noir Bleuté
50% Vert Clair, 45% Vert Foncé, 5% Vert Bizarre

Mon but est d'obtenir ça:

Code :

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

1
2
3
95% <a href="lien_rouge.htm">Rouge</a>, 5% <a href="lien_vert.htm">Vert</a>
100% <a href="lien_noir_bleute.htm">Noir Bleuté</a>
50% <a href="lien_vert_clair.htm">Vert Clair</a>, 45% <a href="lien_vert_fonce.htm">Vert Foncé</a>, 5% <a href="lien_vert_bizarre.htm">Vert Bizarre</a>

Je suppose qu'il y a une possibilité de requête pour réaliser cette modification puisqu'il y a une logique:
-les début des liens html sont situés toujours après "% "
-les fins des liens html sont situés toujours avant une virgule ou après le dernier caractère.
-les adresse des liens sont le mot en mininuscule, sans les accents

En me promenant sur ce forum, j'a vu que certains étaient très forts en manipulation de chaines de caractère,
que pensez-vous de cette requête ?

**xdescamp** · 31/07/2009, 10h44

Bonjour,

C'est effectivement un peu compliqué.
J'ai essayé de le faire avec des expressions régulières, mais sans succès (je n'arrivais pas à modifier le texte pour obtenir le nom de la page).
Je pense que la solution la plus simple (en terme de lisibilité en tout cas) serait de faire ça en PL/SQL.

En SQL pur, j'ai réussi à le faire avec la requête suivante :

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
WITH v AS (SELECT 1 id, '95% Rouge, 5% Vert' col FROM dual UNION ALL
           SELECT 2 id, '100% Noir Bleuté' col FROM dual UNION ALL
           SELECT 3 id, '50% Vert Clair, 45% Vert Foncé, 5% Vert Bizarre' col FROM dual)
SELECT /* On reconcatène les différents enregistrements comme à l'origine */
        id, col,
       (   MAX(DECODE(num, 1, new_valeur))
        || MAX(DECODE(num, 2, ','||new_valeur))
        || MAX(DECODE(num, 3, ','||new_valeur))
        || MAX(DECODE(num, 4, ','||new_valeur))
        || MAX(DECODE(num, 5, ','||new_valeur))
        || MAX(DECODE(num, 6, ','||new_valeur))
        || MAX(DECODE(num, 7, ','||new_valeur))
        || MAX(DECODE(num, 8, ','||new_valeur))
        || MAX(DECODE(num, 9, ','||new_valeur))
        || MAX(DECODE(num, 10, ','||new_valeur)) ) new_col
FROM (/* On construit la nouvelle valeur */
      SELECT id, col,
             num,
             pct||' <a href="'||nom_page||'.htm">'||texte||'</a>' new_valeur
      FROM (/* On construit les différents éléments de la nouvelle valeur */
            SELECT id, col,
                   num,
                   SUBSTR(valeur, 1, instr(valeur, '%')) pct,
                   TRIM(SUBSTR(valeur, INSTR(valeur, '%')+1)) texte,
                   TRANSLATE(LOWER(TRIM(SUBSTR(valeur, INSTR(valeur, '%')+1))), ' éèêà', '_eeea') nom_page
            FROM (/* On découpe la chaine en plusieurs enregistrements */
                  SELECT id, col,
                         LEVEL num,
                         CASE WHEN LEVEL = 1 AND INSTR(v.col, ',', 1, LEVEL) = 0
                                THEN v.col
                              WHEN LEVEL = 1
                                THEN SUBSTR(v.col, 1, INSTR(v.col, ',', 1, LEVEL)-1)
                              WHEN INSTR(v.col, ',', 1, LEVEL) = 0
                                THEN SUBSTR(v.col,
                                            INSTR(v.col, ',', 1, LEVEL - 1) + 1)
                              ELSE SUBSTR(v.col,
                                          INSTR(v.col, ',', 1, LEVEL - 1) + 1,
                                          INSTR(v.col, ',', 1, LEVEL) - INSTR(v.col, ',', 1, LEVEL - 1) -1 )
                         END valeur
                  FROM v
                  CONNECT BY LEVEL <= ( LENGTH(v.col) - LENGTH(REPLACE(v.col,',')) + 1 )
            )
      )
)
GROUP BY id, col
ORDER BY id;
 
ID COL                                             NEW_COL
-- ----------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------
1  95% Rouge, 5% Vert                              95% <a href="rouge.htm">Rouge</a>, 5% <a href="vert.htm">Vert</a>
2  100% Noir Bleuté                                100% <a href="noir_bleute.htm">Noir Bleuté</a>
3  50% Vert Clair, 45% Vert Foncé, 5% Vert Bizarre 50% <a href="vert_clair.htm">Vert Clair</a>, 45% <a href="vert_fonce.htm">Vert Foncé</a>, 5% <a href="vert_bizarre.htm">Vert Bizarre</a>

Le principe consiste en gros à découper chaque chaine en plusieurs enregistrements en utilisant la virgule comme séparateur.
Ensuite on génère la nouvelle valeur pour chaque "enregistrement".
Enfin on reconcatène le tout. Ca fonctionne jusqu'à 10 couleurs. Si tu en veux plus, il faut continuer à copier/coller la partie de concaténation.

**Pomalaix** · 31/07/2009, 11h05

Envoyé par xdescamp

En SQL pur, j'ai réussi à le faire avec la requête suivante ...

chapeau l'ami, ça c'est du grand art !!

Pour ajor : merci de ne pas lancer une même discussion à plusieurs endroits, comme vous l'avez fait ici :
http://www.developpez.net/forums/d78...cteres-oracle/

**xdescamp** · 31/07/2009, 11h41

Envoyé par Pomalaix

chapeau l'ami, ça c'est du grand art !!

Merci

N'empêche que je ne voudrais être à la place du gars qui va récupérer ça le jour où il y a un problème! Ca a intérêt à être très bien commenté pour être maintenable.

**ajor** · 31/07/2009, 12h59

merci xdescamp!

Désolé pour le double post, je pensais m'être trompé de forum, et j'ai oublié d'effacer le premier...

J'ai commencé avec la solution de Pomalaix, je continue donc la discussion ici:
http://www.developpez.net/forums/d78...cteres-oracle/

**Pomalaix** · 30/07/2009, 16h17

Bonjour

Quelle est votre version d'Oracle (à préciser dans toute question) ?

**ajor** · 30/07/2009, 16h23

La version d'Oracle (en détail...)

----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.1.0.3.0 - Productio
NLSRTL Version 10.1.0.3.0 - Production

Et j'utilise SQL*Plus: Release 10.1.0.2.0

**Pomalaix** · 31/07/2009, 02h47

En SQL pur, ça va être raide étant donné que vous avez des valeurs composées.
Ca peut se traiter par une fonction récursive dans le genre de cette amorce, à compléter pour introduire la gestion des exceptions, la conversion de tous les caractères exotiques dans le TRANSLATE, etc.

En l'état, ça permet déjà d'obtenir le bon résultat pour vos exemples.

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
 
create or replace function bricole(liste varchar2) return varchar2
is
        fragment        varchar2(100);
        pos_virgule     number(4);
        val_virgule 	varchar2(2);
        suite           varchar2(4000);
        pos_pct         number(4);
        val_pct         varchar2(10);
        couleur         varchar2(30);
        couleur_fmt 	varchar2(30);
        resultat        varchar2(4000);
 
begin
        -- condition de terminaison
        if liste is null then
                return '';
        end if;
 
        -- découpage
        pos_virgule:=instr(liste, ',');
		if pos_virgule=0 then
			pos_virgule:=length(liste) + 1;
		end if;
		fragment:=substr(liste, 1, pos_virgule-1);
		-- on ne prend ni la virgule, ni l'espace qui la suit
		suite:=substr(liste, pos_virgule + 2);
		if suite is not null then
			val_virgule:=', ';
		else
			val_virgule := '';
		end if;
 
        -- traitement du premier fragment
        pos_pct:=instr(fragment, '%');
        val_pct:=substr(fragment, 1, pos_pct + 1);
        couleur:=substr(fragment, pos_pct + 2);
        couleur_fmt:=translate(lower(replace(couleur, ' ', '_')), 'àÀäÄâÂéÉèÈëËêÊ', 'aaaaaaeeeeeeee');
        resultat:=val_pct || '<a href="lien_' || couleur_fmt || '.htm">' || couleur || '</a>';
 
		-- appel récursif sur la suite de la chaîne
        return resultat || val_virgule || bricole(suite);
end;
/

**ajor** · 31/07/2009, 10h08

Whoa!
C'est encore plus compliqué que je croyais!

Merci beaucoup Pomalaix,

Et comment fait-on pour que cette opération se fasse sur toute une colonne d'une table ? (disons la colonne "Colour" de la table "T_Peinture")

Désolé je connais pas trop, j'ai appris que le SQL classique...

Une dernière précision : ma table contient environ 2000 enregistrements, c'est ok niveau lourdeur de requête ?