比較字串相似度

在 SQL Server 資料庫中清理資料

Miriam Antona

Software Engineer

問題描述

混亂的字串

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

SOUNDEX

DIFFERENCE

SOUNDEX

SOUNDEX ( character_expression )

語音式演算法
回傳四字元代碼
以英語為基礎，但也適用於多種其他語言的單字

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 的運作方式

範例：「Illinois」

保留單字的第一個字母
第一個字母之後，把母音與字母「h」「w」「y」替換為 0（零）

「Illinois」-> I

「Illinois」-> Ill0n00s

SOUNDEX 的運作方式

範例：「Illinois」

第一個字母之後的子音改為數字

SOUNDEX 的運作方式

範例：「Illinois」

第一個字母之後的子音改為數字

字母組	代表數字
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 的運作方式

相鄰相同數字合併為一個
移除所有 0（零）
若字母的數字與第一個數字相同，則移除第一個數字
若代碼少於 3 個數字，補 0 至滿
若代碼多於 3 個數字，刪除最後多餘數字

「I4405002」-> I40502

「I40502」-> I452

「I452」（不再處理）

SOUNDEX - 特例

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

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

SOUNDEX - 檢查相似度

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 - 檢查相似度

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 )

比較兩個 SOUNDEX 值
回傳 0 到 4 的數值
- 0 -> 幾乎不相似
- 4 -> 非常相似或完全相同

DIFFERENCE

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

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

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

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

DIFFERENCE - 檢查相似度

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         |

一起來練習吧！

在 SQL Server 資料庫中清理資料