Requête ORACLE avec tolérance aux erreurs de saisie utilisateur [Résolu]

[olreak]

Bonjour,

Je dois faire des requêtes sur un base ORACLE afin de rechercher, entre autres, des noms et prénoms en laissant la possibilité à l'utilisateur de commettre quelques erreurs. Typiquement, être indifférent aux caractères accentués et à la casse, mais aussi ne pas tenir compte des lettres en double qui se suivent, les espaces, les tirets...

Pour l'indifférence à la casse et aux caractères accentués j'ai trouvé les paramètres NLS_COMP et NLS_SORT, ou alors en utilisant la fonction NLSSORT directement dans mes requêtes.

Cependant je n'ai pas trouvé de moyen simple supprimer les lettres en double, les espaces, tirets...
TRIM ne supprime les espaces qu'en début et fin de chaine
SOUNDEX est basé sur les consonnance anglophone

Bref, le seul moyen qu'il y aurai serait de faire une recherche par expression régulière à fabriquer à la volée en fonction des données de la requête. Le problème c'est que, du coup il faudra que gère les accents à la main dans les regexp (et non plus utiliser les NLS_SORT), que je parse la requête initiale pour ajouter des cardinalités sur chaque caractère... et j'ai peur que les requête soient beaucoup moins efficaces en temps, sachant qu'il y a pratiquement des requêtes en permanence sur le serveur.

Une autre solution consisterait à définir une fonction PL/SQL réalisant se genre de chose, mais il y a plusieurs serveurs et je n'ai la main sur aucun d'eux directement, en tout cas pas avec les droits suffisants pour installer des choses sur le serveur. En revanche j'ai le contrôle sur les services qui font les requêtes puisque c'est moi qui les développe.


Un SOUNDEX ou PHONEX avec consonance française ce serait bien mais ça ne semble pas inclut dans ORACLE, et puis les prénoms et noms de familles ne sont pas toujours à consonance française, même en France.

Bref, est-ce que quelqu'un aurait une meilleure idée, parce que j'imagine que la recherche tolérante aux erreurs dans une base Oracle est assez classique, non?


Merci

[Yanika_bzh]

Soundex existe dans ORACLE.
Un sujet (parmi tant d'autres) ici
Bon courage

[Garuda]

Voir du coté de UTL_RAW.cast_to_varchar2 et NLS_SORT (binary_ai) et les expression régulières
Exemple de code dans un trigger pour voir si un texte a changé en oubliant les espaces et les accents

Code :

1
2
3
4
5
6
7
8
9
10
 
 -- comparaison des commentaitres commerces sans accenst (NLS_SORT) et sans ponctuation ni espaces (REGEXP_REPLACE)
      IF
     ( UTL_RAW.cast_to_varchar2 (
            (NLSSORT (REGEXP_REPLACE (:new.obs_com, '[[:space:]|[:punct:]]*', ''), 'nls_sort=binary_ai'))) <>
            UTL_RAW.cast_to_varchar2 (
               (NLSSORT (REGEXP_REPLACE (:old.obs_com, '[[:space:]|[:punct:]]*', ''), 'nls_sort=binary_ai')))
      )
      THEN
....

[olreak]

Merci pour ces deux réponses,

en fait c'est un peu ce que je cherche à éviter:
- SOUNDEX parce que présent uniquement en prononciation anglophone dans ORACLE, et de toute façon un "é" et un "e" se prononcent différemment alors que je veux les confondre, en en plus ça retourne un code et pas une chaîne de caractère (donc pas possible d'enchaine avecune autre fonction)
- la REGEXP, justement c'est j'aurai aimé éviter pour une question de performance

Merci quand même pour vos réponse, en fait pas certain qu'il y ait une solution simple vu que je vois qu'il semble se vendre des logiciels spécialisés pour effectuer la recherche de doublons "flous" sur une base ORACLE.

[Garuda]

Si tu ne veux pas utiliser REGEXP, tu peux quand meme ignorer les accents avec UTL_RAW et NLS_SORT !

Code :

1
2
3
4
5
 
SELECT 
UTL_RAW.cast_to_varchar2 (
            (NLSSORT ('éaèùà','nls_sort=binary_ai')))
FROM dual

[olreak]

oui, oui, cf mon premier post

Mais NLSSORT a le même problème que SOUNDEX, qui est de renvoyé un code qui lui est propre et pas une chaine de cararactère qui puisse être traitable par d'autres fonction.

Par ailleurs, pour info, plutôt que d'utiliser la fonction NLSSORT il est plus performant de faire des alter session pour positionner NLS_COMP à LINGUISTIC et NLS_SORT à FRENCH_AI (ou autrechose_AI)

[mnitu]

Envoyé par olreak

...Typiquement, être indifférent aux caractères accentués et à la casse, mais aussi ne pas tenir compte des lettres en double qui se suivent, les espaces, les tirets...
...

Filtrez en deux étapes:
1) Lettre en double: regexp.
2) Autres (caractères accentués, les espaces, les tirets): Translate.

[Waldar]

Si vous ne traitez pas de volumes trop important, vous pouvez regarder les fonction du package UTL_MATCH.

J'ai utilisé pour des problèmes similaires l'algorithme de Jaro-Winkler (pas parfaitement conforme à l'original cela dit), qui donne un score :

Code :

1
2
3
4
5
6
SELECT 100 * utl_match.jaro_winkler('olreak', 'olrik') AS score
  FROM dual;
 
     SCORE
----------
     87.56

[olreak]

Merci pour cette réponse,

Je viens de faire quelques tests.
D'une part, par bonheur, les paramètres de session NLS_COMP et NLS_SORT sont pris en compte par REGEXP_LIKE et TRANSLATE,comme ça le problème des accents et majuscule est réglé.

Pour le reste:
1) je dois transformer par la données de comparaison fournie par l'utilisateur en regexp à construire intelligemment
2) REGEXP_LIKE sur le TRANSLATE de la colonne de la base, le translate étant utiliser ici pour supprimer tous les espaces, tirets...

Reste à construire cette regexp automatiquement et intelligemment...

Merci