Extraire une chaine de caractères dans une colonne [Résolu]

[Djene]

Bonjour à tous,

J'ai besoin d'extraire des chaines de caractère d'une colonne d'une table. Simplement la difficulté est que la chaine à extraire peut exister en plusieurs exemplaire dans cette même colonne et noyée avec d'autres informations dont je ne dois pas m'occuper.
La ou les chaînes à extraire est du format :
'Special MonnaieDeChange Montant' soit par exemple ('Special EUR 0.445')

J'ai essayé en utilisant des SUBSTR, INSTR etc... et j'obtiens des résultats qui ne me satisfont pas car cela marche pour un cas (si on n'a qu'une fois 'Special EUR 0.445' ) alors qu'il est possible d'en rencontrer plusieurs avec des montants et des monnaies de change différents.

Exemple dans une même colonne, ce serait sous la forme :
'Capital repay EUR 0.4 + Special USD 0.67 + Special GBP 0.556'.

Seuls les chaines 'Special USD 0.67' et 'Special GBP 0.556' sont à détecter et une fois détectées, il faut arriver à extraire chaque montant et chaque monnaie.

J'espère avoir été assez clair. Je demande votre aide car j'ai passé la journée à tenter ces extractions, hélas sans succès et cela devient urgent.

Merci par avance pour votre aide.
Djene

[Waldar]

Quelle est votre version d'Oracle ?

Edit : Desfois que vous soyez en 10g+, vous pouvez utiliser les expressions régulières.

Ça pourrait donner quelque chose comme ça, probablement à adapter encore un peu :

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
37
38
39
40
41
WITH MaTable AS -- Données de départ
(
SELECT 1 AS cid, 'Capital repay EUR 0.4 + Special USD 0.67 + Special GBP 0.556' AS col FROM dual union ALL
SELECT 2       , 'Special EUR 0.445'                                                   FROM dual union ALL
SELECT 3       , 'Special GBP 0.597 + Capital repay EUR 0.4 + Special USD 0.712456'    FROM dual
)
  ,  Lst AS -- Nombre d'occurences max 
(
SELECT max(length(col) - length(REPLACE(col, '+', '')) + 1) AS nb_occ
  FROM MaTable
)
  , Cpt AS -- Génération de nombres de 1 à nb_occurences
(
    SELECT level AS nm
      FROM Lst
connect BY level <= nb_occ
)
  ,  Reg AS -- Utilisation de regexp pour extraire la partie de la chaîne qui nous intéresse
(
SELECT cid
     , nm AS pos
     , regexp_substr(col, 'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*', 1, nm) AS ext
  FROM MaTable
       CROSS JOIN Cpt
)
  SELECT cid -- Mise en forme finale 
       , pos
       , substr(ext, 9, 3) AS monnaie
       , substr(ext, 13)   AS montant
    FROM Reg
   WHERE ext IS NOT NULL
ORDER BY cid ASC
       , pos ASC;
 
CID POS MONNAIE MONTANT
--- --- ------- --------
1   1       USD 0.67
1   2       GBP 0.556
2   1       EUR 0.445
3   1       GBP 0.597
3   2       USD 0.712456

[Djene]

Grand merci pour votre aide Waldar! Grace à votre aide, j’ai pu avancer à pas de géant.
En fait après investigation, le problème s’avère être encore plus simple car dans la dite colonne nous rencontrerons un flot d’informations mais une seule occurrence de ‘Special EUR 0.67’ par exemple et ce sera juste celui là qu’il faudra extraire. J’ai réussi à modifier votre code et j’ai obtenu le résultat escompté. Simplement, à présent je ne sais comment implémenter tout ceci dans mon code d’origine.
A savoir, il s’agit d’interpréter une colonne particulière lors de chargements de données vers une table temporaire (table TOTO). C'est-à-dire que une fois les données brutes insérées dans TOTO, après transformation, il me faut insérer les résultats dans 3 autres colonnes de cette même table TOTO.

La table TOTO est composée de différentes colonnes (col1, col2, col3, col4, col5, col6 ….). Disons que c’est la col2 qui contiendra l’information brute : ‘Special EUR 0.67’.
1/ C’est de cette col2 qu’il me faut récupérer (en appliquant la solution que vous m’avez donné) l’information monnaie et l’information montant afin de renseigner la col3 avec ‘EUR’ et la col4 avec le montant 0.67.

2/ Autre difficulté, en même temps, le montant 0.67 doit être transformé (en lui appliquant un taux de change USD par ex.) et le résultat inséré dans col5.

Je vous remercie encore une fois d’avance pour votre précieuse aide.
Je reviens dans le code après plusieurs années et j’ai du mal en ce moment.
Votre aide me sera donc très précieuse.

Encore grand merci !

[Waldar]

Je comprends à peu près ce que vous voulez faire, mais je ne le comprends pas précisément.
Si vous pouviez illustrer de quelques lignes d'exemples, avec les données sources, la ou les étapes qui construisent toto, ainsi que la finalité puisque toto est un étape intermédiaire !

[boussafi]

Djene, je m'excuse pour l'intervention.
j'ai quelque chose à vous(forumistes) dire.
j'avoue que Waldar est maitre de SQL avec cette magique requete.

[Waldar]

Merci boussafi, c'est surtout de la pratique et de la bonne lecture !

[Djene]

Bonjour,

Je vais tenter d'être plus précise.
TOTO est donc une table temporaire de la base oracle comportant plusieurs colonnes qui seront renseignées via le chargement batch de différents fichiers. Les fichiers d'origine sont des fichiers xls ayant des colonnes à l'image de TOTO. La col2 existe donc dans le fichier et son contenu (par ex. 'Capital EUR 0.444 + Special EUR 0.67') sera téléchargé à l'identique dans la col2 de la table TOTO.
Après la fin du téléchargement, on sait que l'info ' Special EUR 0.67 ' de la col2, est à extraire si la col1 de TOTO contient l'information 'SPEC_DIV'.

Les données extraites de col2 (c'est à dire la monnaie 'EUR' et le montant '0.67') seront insérées dans la col3 et col4 de TOTO, colonnes restées vides après le téléchargement batch des fichiers.
Au montant de '0.67' on applique un taux de change en USD. Le résultat du calcul sera inséré dans la col6 de TOTO.

Dans la col5 de TOTO, sera inséré le résultat du calcul du montant '0.67' transformé (car on lui aura appliqué le taux de change permettant de passer de 0.67 EUR à un montant USD).

Illustration chargement des données du fichier dans les colonnes de TOTO:

Code :

1
2
 
INSERT INTO TOTO (col1, col2, col3, col4, col5, col6...) AS SELECT (col1, col2, col3, col4, col5, col6 ...) FROM fichiers

Une fois chargement dans TOTO ok :

Code :

1
2
3
4
 
Si col1 contient l'info 'SPEC_DIV'
alors rechercher la chaine de caractère ' Special EUR 0.67 ' dans col2,
extraire de cette chaine la monnaie 'EUR' et le montant '0.67' et les insérer dans col3 et col4 réciproquement

Appliquer taux et insérer dans col5 de TOTO:

Code :

1
2
 
avant d'insérer le montant '0.67', lui appliquer le taux de change en USD et insérer le résultat du calcul dans la col5 de TOTO

J'espère que c'est un peu plus clair ainsi.
Encore merci pour tout.

[Waldar]

En fait que je pense que toute votre table TOTO n'est pas utile.
Tout ce que vous voulez récupérer vous pouvez l'obtenir avec une seule requête SQL qui pointerait directement sur votre fichier.

Néanmoins si pour certaines raisons vous devez garder votre table temporaire, ce que je peux comprendre, pour créer différents niveaux n'analyse il vaut mieux avoir plusieurs tables qu'on rempli par étape plutôt qu'une seule qu'on met à jour, et ce sera d'autre plus vrai que vous avez beaucoup de données.

Est-ce que vous avez une table de référence des monnaies et une autre avec les taux de change ?

[Djene]

Bonjour et merci pour votre réponse rapide.

En fait j'interviens au niveau d'un processus de chargement de fichiers dans des tables dites tables externes, qui à leur tour viennent charger des tables temporaires dont (VALUATION_TEMP) qui est une table intermédiaire avant le chargement de la table finale de travail (VALUATION) qui est à l'image de TOTO.
Mon rôle consiste à traiter les données avant qu'elles ne soient loadées dans la table de travail VALUATION.

Donc:
- Chaque fichier est préalablement chargé dans la table temporaire
- Les colonnes suivantes sont les vraies colonnes des tables VALUATION_TEMP et VALUATION : EVENT_TYPE;

EVENT_DESC;
DIV_QUALDIV;
EVENT_AMOUNT;
TERMS;
TERMSDATE;
STATUS;
SPECIAL_DIV_IN_PROV_CCY;
SPECIAL_DIV_PROV_CCY;
SPECIAL_DIV_IN_SEC_CCY

- Je dois implémenter dans le loader les règles d'extraction de montants spéciaux: La mention 'SPEC_DIV' dans la colonne EVENT_TYPE indique qu'il s'agit d'un montant spécial. Son montant est à extraire de la colonne TERMS. Cette colonne est une chaîne de caractère dont le format est "Special CurrencyISOCode Montant".
D'où la solution d'extraction que vous m'avez donné avec les expressions régulières.

- Le montant devra être converti dans la monnaie du titre.
Si la monnaie de référence du titre est différente de celle du montant spécial, il faut appliquer le taux de change entre la monnaie du montant spécial et celle du titre(information que on trouve dans une autre colonne (quotation_ccy) de la table VALUATION_TEMP. Il faudra appliquer un taux de change sur les montants spéciaux extraits.

Et pour répondre à votre question, le taux sera "remonté" via une vue v_exchange_rate de la base.

Je dois maintenant arriver à extraire la monnaie et le montant de la colonne TERMS (grâce à votre solution avec les expressions régulières) si elle contient un montant spécial et avant de les insérer dans les colonnes (SPECIAL_DIV_IN_PROV_CCY,SPECIAL_DIV_PROV_CCY). La colonne SPECIAL_DIV_IN_SEC_CCY contiendra le montant transformé le cas échéant par le taux de change appliqué.

Merci pour votre intérêt et surtout pour votre aide.

Djene

[Waldar]

Quelques nouvelles pistes.
Si vous n'avez qu'une seule occurrence "Special", vous pouvez laissez tomber les expressions régulières et partir sur une solution avec les fonctions substr / instr.
C'est moins "joli", mais plus performant :

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
WITH MaTable AS
(
SELECT 1 AS cid, 'Capital repay EUR 0.4 + Special USD 0.67' AS col FROM dual UNION ALL
SELECT 2       , 'Special EUR 0.445'                               FROM dual UNION ALL
SELECT 3       , 'Special GBP 0.597 + Capital repay EUR 0.4'       FROM dual UNION ALL
SELECT 4       , 'Capital repay EUR 0.4'                           FROM dual
)
  ,  tmp AS
(
SELECT cid, col,
       case instr(col, 'Special')
         when 0
         then NULL
         else substr(col, instr(col, 'Special'), coalesce(nullif(instr(substr(col, instr(col, 'Special')), ' ', 1, 3)-1, -1), 99))
       end AS terms
  FROM MaTable
)
SELECT cid, col, terms
     , substr(terms, instr(terms, ' ', 1, 1) + 1, instr(terms, ' ', 1, 2) - instr(terms, ' ', 1, 1) - 1) AS monnaie
     , substr(terms, instr(terms, ' ', 1, 2) + 1) AS valeur
  FROM tmp;
 
       CID COL                                       TERMS              MONNAIE VALEUR 
---------- ----------------------------------------- ------------------ ------- -------
         1 Capital repay EUR 0.4 + Special USD 0.67  Special USD 0.67   USD     0.67   
         2 Special EUR 0.445                         Special EUR 0.445  EUR     0.445  
         3 Special GBP 0.597 + Capital repay EUR 0.4 Special GBP 0.597  GBP     0.597  
         4 Capital repay EUR 0.4

L'idée c'est de tout écrire directement dans le code.
À partir de ce résultat, faites une jointure sur la vue v_exchange_rate pour convertir les montants.

L'idée générale c'est d'avoir le moins d'étapes possibles :

1. Chargement des fichiers dans une table dédiée (idéalement, à partir d'une table externe)
2. Création d'une vue à partir de la table précédente qui entre fonctions et jointures vous donne en une seule opération les résultats de votre table finale à mettre dans VALUATION_TMP, mais avec toutes les colonnes déjà remplies.
3. Une dernière étape de fusion de ces données de VALUATION_TMP dans VALUATION

[Djene]

Grand merci Waldar pour votre aide!

Je vais tenter d'implémenter le tout en utilisant un MERGE :

Code :

 MERGE INTO ... USING ( SELECT ...) FROM ...

... j'ai encore du pain sur la planche et je pense que j'aurais en cours de route encore besoin d'aide sur ce forum.

Encore grand merci !

Djene

[Djene]

Bonjour,

Je reviens vers vous avec la requête suivante qui ne fonctionne pas. Il me renvoie le msg: ORA-00905: missing keyword :

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT vt.currency_code,vt.terms,
       CASE vt.event_type WHEN 'SPEC_DIV' THEN 
         substr(regexp_substr(vt.terms, 'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*', 1), 9, 3) AS monnaie,
         substr(regexp_substr(vt.terms, 'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*', 1), 13) AS valeur
         ELSE NULL 
         END AS TERMS
FROM valuation_temp vt
WHERE vt.termsdate = vt.evaluation_date
AND vt.STATUS = 'Implemented'

Pouvez-vous SVP me corroger et me dire ce qui ne va pas ? Merci!

NB: La colonne TERMS contient une chaine de carctère de format "Special CurrencyISOCode Montant". Ex. 'Capital repay EUR 0.4 + Special USD 0.67'

Merci pour votre précieuse aide

Djene

[ORA-007]

Bonjour,

La syntaxe du case est fausse

Code :

1
2
3
4
5
       CASE vt.event_type WHEN 'SPEC_DIV' THEN 
         substr(regexp_substr(vt.terms, 'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*', 1), 9, 3) AS monnaie,
         substr(regexp_substr(vt.terms, 'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*', 1), 13) AS valeur
         ELSE NULL 
         END AS TERMS

[Djene]

SVP, quelle est la bonne syntaxe alors ?

Merci !

[ORA-007]

Bonjour,

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT vt.currency_code,
       vt.terms,
       CASE vt.event_type
         WHEN 'SPEC_DIV' THEN
          substr(regexp_substr(vt.terms,'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*',1),9,3)
         ELSE
          NULL
       END MONNAIE,
       CASE vt.event_type
         WHEN 'SPEC_DIV' THEN
          substr(regexp_substr(vt.terms,'Special [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*',1),13)
         ELSE
          NULL
       END VALEUR
  FROM valuation_temp vt
 WHERE vt.termsdate = vt.evaluation_date
   AND vt.STATUS = 'Implemented'

[Djene]

C'est super cool !!!

Merci beaucoup !

Djene