Comparer la similarité entre des chaînes

Nettoyer des données dans des bases de données SQL Server

Miriam Antona

Software Engineer

Décrire le problème

Chaînes brouillon

| airport_state |
|---------------|
| Caalifornia   |
| California    |
| Californiaa   |
| Illinois      |
| Ilynois       |
| Tejas         |
| Texas         |

SOUNDEX

DIFFERENCE

SOUNDEX

SOUNDEX ( character_expression )

Algorithme phonétique
Retourne un code à quatre caractères
Basé sur l'anglais, mais fonctionne aussi avec de nombreux mots d'autres langues

SELECT SOUNDEX('Illinois') AS soundex_code1;
SELECT SOUNDEX('Ilynois') AS soundex_code2;
SELECT SOUNDEX('California') AS soundex_code3;

| soundex_code1 |   | soundex_code2 |   | soundex_code3 |
|---------------|   |---------------|   |---------------|
| I452          |   | I452          |   | I416          |

SOUNDEX — fonctionnement

Exemple : « Illinois »

Écrit la première lettre du mot
Remplace les voyelles et les lettres « h », « w », « y » par zéro (0) après la première lettre

"Illinois" -> I

"Illinois" -> Ill0n00s

SOUNDEX — fonctionnement

Exemple : « Illinois »

Remplace les consonnes après la première lettre

SOUNDEX — fonctionnement

Exemple : « Illinois »

Remplace les consonnes après la première lettre

Lettres	Représenté par
b, f, p, v	1
c, g, j, k, q, s, x, z	2
d, t	3
l	4
m, n	5
r	6

"Ill0n00s" -> I4405002

SOUNDEX — fonctionnement

Remplace les chiffres identiques adjacents par un seul
Supprime tous les zéros (0)
Si le chiffre de la lettre est identique au premier chiffre, supprime le premier chiffre
Ajoute des zéros si le code a moins de 3 chiffres
Supprime les derniers chiffres si le code en a plus de 3

"I4405002" -> I40502

"I40502" -> I452

« I452 » (ne pas appliquer)

SOUNDEX — exceptions

SELECT SOUNDEX('Arizona') AS soundex_code1;
SELECT SOUNDEX('Arkansas') AS soundex_code2;

| soundex_code1 |   | soundex_code2 |
|---------------|   |---------------|
| A625          |   | A625          |

SOUNDEX — vérifier les similarités

SELECT DISTINCT A1.airport_state
FROM airports A1 
INNER JOIN airports A2 
    ON SOUNDEX(A1.airport_state) = SOUNDEX(A2.airport_state)
    AND A1.airport_state <> A2.airport_state

| airport_state |
|---------------|
| Caalifornia   |
| California    |
| Californiaa   |
| Illinois      |
| Ilynois       |
| New Jersey    |
| New York      |
| Tejas         |
| Texas         |

SOUNDEX — vérifier les similarités

SELECT DISTINCT A1.airport_state
FROM airports A1 
INNER JOIN airports A2 
    ON SOUNDEX(REPLACE(A1.airport_state, ' ', '')) = SOUNDEX(REPLACE(A2.airport_state, ' ', ''))
    AND A1.airport_state <> A2.airport_state

"New York" -> "NewYork"

| airport_state |
|---------------|
| Caalifornia   |
| California    |
| Californiaa   |
| Illinois      |
| Ilynois       |
| Tejas         |
| Texas         |

DIFFERENCE

DIFFERENCE ( character_expression , character_expression )

Compare deux valeurs SOUNDEX
Retourne une valeur de 0 à 4
- 0 -> peu ou pas de similarité
- 4 -> très similaire ou identique

DIFFERENCE

SELECT DIFFERENCE('Illinois', 'Ilynois') AS dif_1;

| dif1 |
|------|
|  4   |

SELECT DIFFERENCE('Illinois', 'California') AS dif_2;

| dif2 |
|------|
| 1    |

DIFFERENCE — vérifier les similarités

SELECT DISTINCT A1.airport_state, A2.airport_state
FROM airports A1 
INNER JOIN airports A2 
    ON DIFFERENCE(REPLACE(A1.airport_state, ' ', ''), REPLACE(A2.airport_state, ' ', '')) = 4
    AND A1.airport_state <> A2.airport_state

| airport_state | airport_state |
|---------------|---------------|
| Caalifornia   | California    |
| Caalifornia   | Californiaa   |
| California    | Caalifornia   |
| California    | Californiaa   |
| Californiaa   | Caalifornia   |
| Californiaa   | California    |
| Illinois      | Ilynois       |
| Ilynois       | Illinois      |
| Massachusetts | Michigan      |
| Tejas         | Texas         |
| Texas         | Tejas         |

Passons à la pratique !

Nettoyer des données dans des bases de données SQL Server