Porównywanie podobieństwa ciągów znaków

Czyszczenie danych w bazach danych SQL Server

Miriam Antona

Software Engineer

Opis problemu

Niepoprawne ciągi znaków

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

SOUNDEX

DIFFERENCE

SOUNDEX

SOUNDEX ( character_expression )

Algorytm fonetyczny
Zwraca czteroznakowy kod
Oparty na języku angielskim, działa też z wieloma słowami w innych językach

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 – jak to działa

Przykład: "Illinois"

Zapisuje pierwszą literę wyrazu
Zastępuje zerami (0) samogłoski oraz litery "h", "w", "y" po pierwszej literze

"Illinois" -> I

"Illinois" -> Ill0n00s

SOUNDEX – jak to działa

Przykład: "Illinois"

Zastępuje spółgłoski po pierwszej literze

SOUNDEX – jak to działa

Przykład: "Illinois"

Zastępuje spółgłoski po pierwszej literze

Litery	Reprezentowane przez
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 – jak to działa

Zastępuje sąsiadujące identyczne cyfry jedną cyfrą
Usuwa wszystkie zera (0)
Jeśli cyfra pierwszej litery jest taka sama jak pierwsza cyfra kodu, usuwa tę cyfrę.
Dodaje zera, jeśli kod zawiera mniej niż 3 cyfry.
Usuwa ostatnie cyfry, jeśli kod ma więcej niż 3 cyfry.

"I4405002" -> I40502

"I40502" -> I452

"I452" (nie stosuj)

SOUNDEX – wyjątki

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

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

SOUNDEX – sprawdzanie podobieństw

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 – sprawdzanie podobieństw

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 )

Porównuje dwie wartości SOUNDEX
Zwraca wartość od 0 do 4
- 0 -> małe lub żadne podobieństwo
- 4 -> bardzo podobne lub identyczne

DIFFERENCE

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

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

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

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

DIFFERENCE – sprawdzanie podobieństw

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         |

Czas na ćwiczenia!

Czyszczenie danych w bazach danych SQL Server