Quand on travaille avec des bases de données, il arrive souvent qu’on ait à comparer des noms mal orthographiés, des doublons masqués, ou des variantes d’écriture. À partir de la version Oracle Database 23ai, deux nouvelles fonctions viennent à notre secours : FUZZY_MATCH et PHONIC_ENCODE.
Pas besoin d’être un expert en Data Quality pour les utiliser : elles sont simples, efficaces et directement disponibles en SQL. Voici comment elles fonctionnent, avec un exemple concret.
1. FUZZY_MATCH – Comparer des textes même s’ils sont différents
FUZZY_MATCH permet de mesurer à quel point deux chaînes de texte se ressemblent. Elle retourne un score de similarité entre 0 et 100. Plus le score est élevé, plus les textes sont proches.
FUZZY_MATCH(algorithme, texte1, texte2)
Oracle met à disposition plusieurs algorithmes de comparaison pour cette fonction, parmi les plus utilisés on retrouve :
- levenshtein : Mesure le nombre minimal de modifications (ajout, suppression, remplacement) pour passer d’une chaîne à une autre.
- jaro_winkler: favorise les débuts de mots similaires. Très efficace pour comparer des prénoms et noms saisis par des utilisateurs.
- bigram: Compare les chaînes par paires de lettres consécutives. Rapide, souvent utilisé pour des recherches approximatives larges.
- whole_word_match: compare avec correspondance exacte
Passons à un exemple pratique, nous allons créer une table Client avec différentes variantes de nom et prénom.
SQL> CREATE TABLE Client (
client_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nom VARCHAR2(100)
);
INSERT INTO Client (nom) VALUES ('Amine ATLAS');
INSERT INTO Client (nom) VALUES ('Amin ATLAS');
INSERT INTO Client (nom) VALUES ('Amine Atlass');
INSERT INTO Client (nom) VALUES ('Ameen Atlas');
INSERT INTO Client (nom) VALUES ('Amime Atllas');
INSERT INTO Client (nom) VALUES ('Amen Atlaz');
INSERT INTO Client (nom) VALUES ('Amene ATLAS');
INSERT INTO Client (nom) VALUES ('Hamene Atlaas');
INSERT INTO Client (nom) VALUES ('Amene Atla');
INSERT INTO Client (nom) VALUES ('Aminé Atlas');
Commit;
Langage du code : PHP (php)Comparons maintenant chaque ligne à "Amine ATLAS" en utilisant l’algorithme “jaro_winkler” :
SELECT client_id, nom,
FUZZY_MATCH(jaro_winkler, nom, 'Amine ATLAS') AS match_score
FROM Client
ORDER BY match_score DESC;
CLIENT_ID NOM MATCH_SCORE
---------- ---------------------------- -----------
1 Amine ATLAS 100
2 Amin ATLAS 98
7 Amene ATLAS 92
3 Amine Atlass 84
10 Aminé Atlas 81
5 Amime Atllas 77
9 Amene Atla 72
6 Amen Atlaz 72
4 Ameen Atlas 71
8 Hamene Atlaas 54Langage du code : PHP (php)2.PHONIC_ENCODE – Comparer les mots par leur prononciation
Parfois, deux mots sont très différents à l’écrit, mais se prononcent presque de la même façon. C’est exactement le rôle de PHONIC_ENCODE. Cette fonction transforme un texte en code phonétique, ce qui permet de comparer les sons plutôt que les lettres.
PHONIC_ENCODE(algorithme, texte)Deux algorithmes sont utilisées pour cette fonction, ils permettent de convertir des mots (généralement des noms) en un code phonétique.
- DOUBLE_METAPHONE : renvoie le code phonétique principal.
- DOUBLE_METAPHONE_ALT : renvoie un code alternatif si une autre prononciation est possible, sinon le code phonétique principal est retourné.
Passons à un exemple pratique:
SELECT client_id, nom,
PHONIC_ENCODE(DOUBLE_METAPHONE, nom) AS phonetic_code
FROM Client;
CLIENT_ID NOM PHONETIC_CODE
---------- ----------------------------------- ----------------
1 Amine ATLAS AMNT
2 Amin ATLAS AMNT
3 Amine Atlass AMNT
4 Ameen Atlas AMNT
5 Amime Atllas AMMT
6 Amen Atlaz AMNT
7 Amene ATLAS AMNT
8 Hamene Atlaas HMNT
9 Amene Atla AMNT
10 Aminé Atlas AMNT
10 lignes sélectionnées.
Langage du code : PHP (php)3.Combiner FUZZY_MATCH et PHONIC_ENCODE
La vraie force arrive quand on combine les deux approches (similarité visuelle + similarité phonétique):
SELECT client_id,nom,
FUZZY_MATCH(jaro_winkler, nom, 'Amine ATLAS') "FUZZY_MATCH",
PHONIC_ENCODE(double_metaphone, nom) "PHONIC_ENCODE"
FROM Client
WHERE PHONIC_ENCODE(double_metaphone, nom)=PHONIC_ENCODE(double_metaphone, 'Amine ATLAS')
AND FUZZY_MATCH(jaro_winkler, nom, 'Amine ATLAS') > 85
ORDER BY nom;
CLIENT_ID NOM FUZZY_MATCH PHONIC_ENCODE
---------- -------------------------- ----------- -------------------
7 Amene ATLAS 92 AMNT
2 Amin ATLAS 98 AMNT
1 Amine ATLAS 100 AMNT Langage du code : JavaScript (javascript)Cette requête est idéale pour :
- la déduplication de clients.
- les recherches tolérantes aux fautes.
- les formulaires utilisateurs mal saisis.
Poster un Commentaire