Confrontare la somiglianza tra stringhe

Pulizia dei dati nei database SQL Server

Miriam Antona

Software Engineer

Descrivere il problema

Stringhe disordinate

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

SOUNDEX

DIFFERENCE

SOUNDEX

SOUNDEX ( character_expression )

Algoritmo fonetico
Restituisce un codice di quattro caratteri
Basato sull’inglese, ma funziona anche con molte parole di altre lingue

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 - come funziona

Esempio: "Illinois"

Scrive la prima lettera della parola
Sostituisce con zero (0) le vocali e le lettere "h", "w", "y" dopo la prima lettera

"Illinois" -> I

"Illinois" -> Ill0n00s

SOUNDEX - come funziona

Esempio: "Illinois"

Sostituisce le consonanti dopo la prima lettera

SOUNDEX - come funziona

Esempio: "Illinois"

Sostituisce le consonanti dopo la prima lettera

Lettere	Rappresentate da
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 - come funziona

Unisce cifre adiacenti uguali
Rimuove tutti gli zeri (0)
Se la cifra della lettera coincide con la prima, rimuove la prima cifra
Aggiunge zeri se il codice ha meno di 3 cifre
Taglia le cifre finali se il codice ha più di 3 cifre

"I4405002" -> I40502

"I40502" -> I452

"I452" (non applicare)

SOUNDEX - eccezioni

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

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

SOUNDEX - verifica delle somiglianze

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 - verifica delle somiglianze

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 )

Confronta due valori SOUNDEX
Restituisce un valore da 0 a 4
- 0 -> poca o nessuna somiglianza
- 4 -> molto simili o identici

DIFFERENCE

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

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

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

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

DIFFERENCE - verifica delle somiglianze

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         |

Ayo berlatih!

Pulizia dei dati nei database SQL Server