Remplacer une string pour une valeur incrémentée

**h12enri** · 25/03/2015, 09h06

Bonjour,
J'espère que tout va bien pour vous ce matin, j'aimerai profiter de votre savoir.

Je souhaiterai remplacer une string (en l'occurrence 'zzz') par un chiffre qui s'incrémente à mesure que ces 3 z apparaissent.
Exemple :

Code :

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

1
2
3
SELECT 'zzz' org FROM DUAL UNION ALL
SELECT 'pofljzzzpiuiuiizzz' FROM DUAL UNION ALL
SELECT 'jhjhjkkzzzpoiuzzz' FROM DUAL

Me retourne évidemment

zzz
pofljzzzpiuiuiizzz
jhjhjkkzzzpoiuzzz

Serait-il possible que les zzz deviennent des chiffres allant de 1 à ... jusqu'à ce qu'il n'y en ai plus ?
Donc que ça me retoune :
1
poflj2piuiuii3
jhjhjkk4poiu5

C'est réalisable ça ?

Merci pour votre aide

Henri

**McM** · 25/03/2015, 10h30

Tu le veux en SELECT ou en UPDATE ?

J'avais dans l'idée d'utiliser une séquence avec un replace, mais le problème est que le replace ne valide la chaine de remplacement qu'une seule fois..

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT org, REPLACE(org, 'zzz', MaSequence.NEXTVAL) antizzz
FROM (SELECT 'zzz' org FROM DUAL UNION ALL
SELECT 'pofljzzzpiuiuiizzz' FROM DUAL UNION ALL
SELECT 'jhjhjkkzzzpoiuzzz' FROM DUAL)
 
 
ORG	Antizzz
zzz	54940
pofljzzzpiuiuiizzz	poflj54941piuiuii54941
jhjhjkkzzzpoiuzzz	jhjhjkk54942poiu54942

Donc a mon avis, il faudra utiliser une fonction qui boucle sur les zzz et qui les transforme un par un en appelant la séquence.

Si c'est pour un SELECT qui va être utilisé plusieurs fois, il faudrait recréer la séquence à 0 à chaque fois.. ce qui n'est pas top, il faudrait alors utiliser une variable de session (variable de package).

**h12enri** · 25/03/2015, 10h36

Salut McM,

Merci pour ta réponse, c'est pour l'utiliser en SELECT oui, pas en UPDATE.

Si j'utilise une séquence comme tu le proposes, comment je crée cela ?

Merci

**aieeeuuuuu** · 25/03/2015, 12h24

Bonjour,

J'ai deux questions : sur quoi vous appuyez vous pour l'ordre de remplacement ?
Par exemple, pourquoi dans votre exemple ne pas obtenir
jhjhjkk1poiu2
poflj3piuiuii4
5

et surtout... pourquoi vouloir faire ça ?
Quel est l’intérêt de tout ça ?

**h12enri** · 25/03/2015, 13h22

Bonjour aieeeuuuuu,

J'ai évidemment simplifié le problème pour ne présenter que l'essentiel, je comprends que vu de l'extérieur ça parait obscur comme but, mais ça a un intérêt certain pour moi. L'expliquer serait trop compliqué et mènerait à embrouiller les esprits.

L'ordre a effectivement de l'importance, ce serait bien que le 1 soit le premier remplacement et ainsi de suite, et pas le contraire, oui.

Si ce n'est pas possible, alors tant pis, j'essayerai de m'en sortir différement, mais ça m'aurait été d'une grande aide.

**aieeeuuuuu** · 25/03/2015, 13h51

Envoyé par h12enri

ce serait bien que le 1 soit le premier remplacement

Le premier par rapport à quoi ?

**h12enri** · 25/03/2015, 15h10

Le premier par rapport à l'ordre que je présente.
Tu as montré un exemple avec l'ordre inversé, mais je souhaiterai que le 1 commence au premier SELECT statement, et pas à partir du dernier SELECT (comme tu l'as illustré dans ta question initiale).

**aieeeuuuuu** · 25/03/2015, 15h30

J'imagine que ces données sont dans une table. Et dans une table, il n'y a pas d'ordre des lignes, c'est pourquoi je pose la question.

Si la numérotation doit se faire selon un ordre précis, alors il faut que cet ordre s'appuie sur quelque chose, comme par exemple une colonne contenant des éléments classables, idéalement uniques (nombres, dates,...)

**McM** · 25/03/2015, 15h41

Voici comment je le ferais :

1/ Créer un package qui permet d'avoir une variable de session (v), et une fonction qui pour une chaine renvoie 'zzz' remplacé par la variable v incrémentée.

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
CREATE PACKAGE WMC AS
 v NUMBER;
 FUNCTION F (p_chaine IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE PACKAGE BODY WMC AS
FUNCTION F (p_chaine IN VARCHAR2) RETURN VARCHAR2
IS
 v_chaine VARCHAR2(2000) := p_chaine;
BEGIN
 WHILE INSTR(v_chaine, 'zzz') > 0
 LOOP
   wmc.v := NVL(wmc.v, 0) + 1;
   v_chaine := SUBSTR(v_chaine, 1, INSTR(v_chaine, 'zzz') - 1) || wmc.v	|| SUBSTR(v_chaine, INSTR(v_chaine, 'zzz') + 3);
 END LOOP;
	RETURN v_chaine; 
END;
END;
/

Ensuite, un simple SQL fait l'affaire.

Code :

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

1
2
3
4
5
6
7
8
9
SELECT org, WMC.f(org) antizzz
FROM (SELECT 'zzz' org FROM DUAL UNION ALL
SELECT 'pofljzzzpiuiuiizzz' FROM DUAL UNION ALL
SELECT 'jhjhjkkzzzpoiuzzz' FROM DUAL)
 
ORG	ANTIZZZ
zzz	1
pofljzzzpiuiuiizzz	poflj2piuiuii3
jhjhjkkzzzpoiuzzz	jhjhjkk4poiu5

Penser à réinitaliser la variable de package entre chaque appel (par session), sinon ça continue à s'incrémenter

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ORG	ANTIZZZ
zzz	6
pofljzzzpiuiuiizzz	poflj7piuiuii8
jhjhjkkzzzpoiuzzz	jhjhjkk9poiu10
 
exec wmc.v := 0;
 
ORG	ANTIZZZ
zzz	1
pofljzzzpiuiuiizzz	poflj2piuiuii3
jhjhjkkzzzpoiuzzz	jhjhjkk4poiu5

**aieeeuuuuu** · 25/03/2015, 17h30

ou sinon, un truc comme ça (attention, ça pique les yeux

)

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
 
WITH LaTable(i , org) AS (
	SELECT 1,'zzz' org FROM DUAL UNION ALL
	SELECT 2,'pofljzzzpiuiuiizzz' FROM DUAL UNION ALL
	SELECT 3,'jhjhjkkzzzpoiuzzz' FROM DUAL
),
Rec(i, l, org, n) AS (
	select i, org as l, CAST(org As VARCHAR(50)) AS org, 1 as n
	from LaTable
	where i = 1
	union ALL
	select 
      LaTable.i,
      LaTable.org, 
      SUBSTR(
              CASE WHEN INSTR(Rec.org,'zzz' ) > 0 THEN rec.org ELSE LaTable.org END 
              ,0
              ,INSTR(
                  CASE WHEN INSTR(Rec.org,'zzz' ) > 0 THEN rec.org ELSE LaTable.org END,
                  'zzz'
              ) -1
       ) 
        ||
      CAST(Rec.n AS VARCHAR(50))
        ||
      SUBSTR(
              CASE WHEN INSTR(Rec.org,'zzz' ) > 0 THEN rec.org ELSE LaTable.org END 
              ,INSTR(
                  CASE WHEN INSTR(Rec.org,'zzz' ) > 0 THEN rec.org ELSE LaTable.org END,
                  'zzz'
                )
              +3 
       )
    ,Rec.n + 1
	from LaTable
	inner join Rec
		on LaTable.i = Rec.i + CASE WHEN INSTR(Rec.org,'zzz' ) > 0 THEN 0 ELSE 1 END
)
select *
from rec
where org not like '%zzz%'

**Pomalaix** · 25/03/2015, 18h32

Envoyé par aieeeuuuuu

... (attention, ça pique les yeux

)

Bravo pour la performance, mais visuellement, c'est juste immonde !

**McM** · 26/03/2015, 09h54

J'avoue ne pas avoir bien compris au premier regard

on peut faire du récursif avec du with ?

Je vais prendre cette requête et l'étudier plus en détail.

**Pomalaix** · 26/03/2015, 10h22

Envoyé par McM

... on peut faire du récursif avec du with ?...

Oui, c'est une nouveauté 11g.

**h12enri** · 26/03/2015, 10h42

Formidable les gars! Merci

Belle peformance, votre aide est précieuse, bravo.

**bstevy** · 27/03/2015, 01h21

Fait attention tt de même, si dans la table temp on a des chaines sans la partie recherchée, ca fonctionne pas super.
Il faut vraiment exclure les chaines non concernées par le traitement.

Remplacer une string pour une valeur incrémentée [11g]

SQL Oracle

Discussions similaires

Partager

Partager