การเปรียบเทียบความคล้ายคลึงระหว่างสตริง

การทำความสะอาดข้อมูลในฐานข้อมูล SQL Server

Miriam Antona

Software Engineer

อธิบายปัญหา

สตริงที่ไม่สะอาด

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

SOUNDEX

DIFFERENCE

SOUNDEX

SOUNDEX ( character_expression )

อัลกอริทึมเสียง (Phonetic)
คืนค่าโค้ด 4 ตัวอักษร
พัฒนาจากภาษาอังกฤษ แต่ใช้ได้กับหลายภาษา

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"

แทนที่พยัญชนะที่อยู่หลังตัวอักษรแรก

Letters	Represented by
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 หลัก
ตัดตัวเลขท้ายออกถ้าโค้ดมีมากกว่า 3 หลัก

"I4405002" -> I40502

"I40502" -> I452

"I452" (don't apply)

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