De gelijkenis tussen strings vergelijken

Gegevens opschonen in SQL Server-databases

Miriam Antona

Software Engineer

Het probleem beschrijven

Rommelige strings

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

SOUNDEX

DIFFERENCE

SOUNDEX

SOUNDEX ( character_expression )

Fonetisch algoritme
Geeft een code van vier tekens terug
Gebaseerd op het Engels, maar werkt ook met veel woorden in andere talen

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 - werking

Voorbeeld: "Illinois"

Schrijft de eerste letter van het woord
Vervangt na de eerste letter klinkers en de letters "h", "w", "y" door nul (0)

"Illinois" -> I

"Illinois" -> Ill0n00s

SOUNDEX - werking

Voorbeeld: "Illinois"

Vervangt medeklinkers na de eerste letter

SOUNDEX - werking

Voorbeeld: "Illinois"

Vervangt medeklinkers na de eerste letter

Letters	Weergegeven als
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 - werking

Vervangt gelijke naast elkaar staande cijfers door één
Verwijdert alle nullen (0)
Als het cijfer van de letter gelijk is aan het eerste cijfer, verwijdert het het eerste cijfer.
Voegt nullen toe als de code minder dan 3 cijfers heeft.
Verwijdert laatste cijfers als de code meer dan 3 cijfers heeft.

"I4405002" -> I40502

"I40502" -> I452

"I452" (niet toepassen)

SOUNDEX - uitzonderingen

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

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

SOUNDEX - gelijkenissen checken

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 - gelijkenissen checken

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 )

Vergelijkt twee SOUNDEX-waarden
Geeft een waarde van 0 t/m 4 terug
- 0 -> weinig of geen overeenkomst
- 4 -> zeer vergelijkbaar of identiek

DIFFERENCE

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

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

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

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

DIFFERENCE - gelijkenissen checken

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         |

Laten we oefenen!

Gegevens opschonen in SQL Server-databases