آموزش Soundex SQL Server جستجوی آوایی

آموزش جامع Soundex در SQL Server: یافتن شباهت‌های آوایی در داده‌ها

SQL Soundex یک الگوریتم آوایی است که کلمات را بر اساس صدای آن‌ها کدگذاری می‌کند، نه املای دقیقشان. این تابع برای یافتن شباهت‌های اسمی و تطبیق داده‌ها که ممکن است خطاهای املایی یا واریانس‌های املایی داشته باشند، بسیار مفید است. هدف اصلی Soundex، ارائه یک روش استاندارد برای فهرست‌بندی نام‌ها بر اساس تلفظ انگلیسی است. با استفاده از Soundex، می‌توانید رکوردها را بر اساس شباهت آوایی مقایسه کنید که در سناریوهای مختلف مانند جستجوی مشتریان یا پاکسازی داده‌ها کاربرد دارد. الگوریتم Soundex به این صورت عمل می‌کند که هر نام را به یک کد چهار کاراکتری متشکل از یک حرف و سه عدد تبدیل می‌کند. این الگوریتم شامل مراحل زیر است:

(Retain the first letter of the name and drop all other occurrences of the same letter.)

سپس سایر حروف به صورت زیر کدگذاری می‌شوند:

(Remove all vowels (A, E, I, O, U, Y), H, W.)

(Assign numbers to the remaining letters as follows:)

• B, F, P, V = 1
• C, G, J, K, Q, S, X, Z = 2
• D, T = 3
• L = 4
• M, N = 5
• R = 6

پس از آن، موارد زیر اعمال می‌شود:

(If two or more letters with the same number are adjacent (from the original name), retain only the first one; also, remove all occurrences of 0 from the string (e.g., if a letter was a vowel and was removed, but a letter before or after it was assigned the same number as an adjacent letter). Keep the first letter.)

و در نهایت:

(Pad with zeros and/or truncate to be exactly four characters long. For example, “A230” becomes “A230” and “A23” becomes “A230” and “A” becomes “A000”.)

تابع `SOUNDEX()` در SQL Server یک مقدار چهار کاراکتری از عبارت ورودی محاسبه و برمی‌گرداند. این تابع فقط یک آرگومان می‌پذیرد که یک عبارت کاراکتری است.

مثال‌هایی از نحوه کار تابع `SOUNDEX()`:


SELECT SOUNDEX('Soundex');
SELECT SOUNDEX('Smith');
SELECT SOUNDEX('Smyth');
SELECT SOUNDEX('Jackson');
SELECT SOUNDEX('Jaxon');
SELECT SOUNDEX('Robert');
SELECT SOUNDEX('Rupert');


نتایج این کوئری‌ها به ترتیب ‘S532’, ‘S530’, ‘S530’, ‘J250’, ‘J250’, ‘R163’, ‘R163’ خواهد بود. همانطور که مشاهده می‌کنید، “Smith” و “Smyth” دارای کد Soundex یکسان هستند که نشان‌دهنده شباهت آوایی آن‌هاست.

علاوه بر `SOUNDEX()`، SQL Server تابع `DIFFERENCE()` را نیز ارائه می‌دهد. این تابع دو عبارت Soundex را مقایسه می‌کند و یک مقدار عدد صحیح از 0 تا 4 را برمی‌گرداند که نشان‌دهنده میزان شباهت آوایی بین دو رشته است. هرچه مقدار بازگشتی بیشتر باشد، شباهت آوایی بین دو رشته بیشتر است. یک مقدار 4 نشان‌دهنده بالاترین شباهت (یعنی کدهای Soundex یکسان) و 0 نشان‌دهنده کمترین شباهت است.

مثال‌هایی از نحوه استفاده از تابع `DIFFERENCE()`:


SELECT DIFFERENCE('Smith', 'Smyth');
SELECT DIFFERENCE('Smith', 'Jones');
SELECT DIFFERENCE('Jackson', 'Jaxon');
SELECT DIFFERENCE('Robert', 'Rupert');
SELECT DIFFERENCE('Soundex', 'Example');


نتایج این کوئری‌ها به ترتیب 4, 1, 4, 4, 0 خواهد بود. این مقادیر به شما کمک می‌کنند تا شباهت آوایی بین نام‌ها را به سرعت ارزیابی کنید.

Soundex در جستجوهای پایگاه داده، به‌ویژه برای داده‌های متنی که ممکن است شامل خطاهای تایپی یا املایی باشند، بسیار کارآمد است. به عنوان مثال، می‌توانید از آن برای یافتن نام‌هایی استفاده کنید که به نظر می‌رسد یکسان تلفظ می‌شوند اما املای متفاوتی دارند.


CREATE TABLE Names (
ID INT PRIMARY KEY IDENTITY(1,1),
FullName VARCHAR(100)
);

INSERT INTO Names (FullName) VALUES
('Smith'),
('Smyth'),
('Jonhson'),
('Jonson'),
('Robert'),
('Robart'),
('Catherine');

SELECT FullName, SOUNDEX(FullName) AS SoundexCode
FROM Names;

SELECT N1.FullName AS Name1, N2.FullName AS Name2, DIFFERENCE(N1.FullName, N2.FullName) AS PhoneticDifference
FROM Names N1, Names N2
WHERE N1.ID = 3;

این مثال‌ها نشان می‌دهند چگونه می‌توانید لیستی از نام‌ها را ایجاد کرده و شباهت‌های آوایی بین آن‌ها را با استفاده از توابع `SOUNDEX()` و `DIFFERENCE()` کشف کنید. کوئری آخر به شما کمک می‌کند جفت نام‌هایی را پیدا کنید که از نظر آوایی شباهت بالایی دارند.

توجه داشته باشید که تابع `SOUNDEX()` تحت تأثیر Collation پایگاه داده قرار می‌گیرد. اگر پایگاه داده شما از یک Collation غیرمرتبط با زبان انگلیسی استفاده کند، نتایج Soundex ممکن است همیشه مطابق انتظار نباشد. برای استفاده بهینه، اطمینان حاصل کنید که از یک Collation مناسب برای زبان انگلیسی استفاده می‌کنید.

همچنین، در سناریوهای با حجم بالای داده، استفاده از Soundex برای فهرست‌بندی و جستجو می‌تواند بر عملکرد تأثیر بگذارد. در برخی موارد، می‌توانید یک ستون محاسبه شده و Persisted برای کد Soundex ایجاد کنید تا از ایندکس‌گذاری و بهبود عملکرد کوئری‌ها بهره‌مند شوید.