قواعد بيانات SQL Server : الاستعلام على الكلمات المتشابهة في اللفظ باستخدام دالة SOUNDEX

هذه المقالة متعلقة بقواعد بيانات Microsoft SQL Server وتناقش النقاط التالية:

-          دالة SOUNDEX

-          دالة DIFFERENCE

-          كتابة استعلام يظهر النتائج المتشابهة مع كلمة ما في اللفظ

-          هل تدعم دالة SOUNDEX اللغة العربية؟ وما هي الحلول المتوفرة؟

-          هل تعتبر دالة SOUNDEX مثالية للتعرف على الكلمات المتشابهة؟

المتطلبات السابقة لقراءة هذه المقالة:

-          إلمام بأساسيات قواعد بيانات Microsoft SQL Server

افرض أن ماجد طلب من موظف استقبال الفندق معرفة رقم الغرفة التي يقطن فيها صديقه محمد الأمين، قام الموظف بالبحث عن محمد بواسطة الاسم الأول فأدخل الاسم التالي Muhamed ، فظهرت للموظف نتائج البحث ومن ضمنها سجل يحمل قيم الاسم الأول والاسم الأخير بالتتالي Mohammed Alameen ورقم غرفته 52 ، أخبر الموظف ماجد بأن صديقه محمد يقطن في الغرفة رقم 52 ، توجه ماجد إلى غرفة محمد وكان اللقاء بعد غياب طويل، تعانقا وبدأ مسلسل الهراء الذي دائما ما يحدث عندما يلتقي الأصدقاء ببعضهم – هلا والله، كيف الحال؟ بشرنا عنك؟ علوومك؟ وينك يا رجال؟.... – وأثناء حديثهما قال ماجد: لقد شاهدت موظف الاستقبال يكتب اسمك بالطريقة التالية muhamed ، هل اسمك بالفعل مدون في إثباتك الشخصي بهذه الطريقة ؟ اندهش محمد وقال لا، بل هو مدون بالطريقة التالية Mohammed ، وقد قام الموظف الذي استقبلني بإدخال بياناتي كما هي مدونة في إثباتي الشخصي! تعجب ماجد ومحمد وتسائلوا: كيف استطاع نظام الفندق التعرف على العملاء الذين أسمائهم الأولى تشبه الاسم الأول الذي استخدمه موظف الاستقبال للبحث عن محمد وهو Muhamed؟ ، كيف نجيب على تساؤلهم؟

بتلك القصة السخيفة أبدأ تمهيدي لهذه المقالة، وأجيب على التساؤل الأخير بأن الإجابة – مع إمكانية تنوع الإجابات -  تكمن في دالة SOUNDEX ، تستخدم هذه الدالة ضمن الاستعلامات المنفذة على قواعد بيانات Microsoft SQL Server لمعرفة النتائج المتشابهة في اللفظ، تستقبل هذه الدالة معطى واحد فقط وهو تعبير نصي وتقوم بإرجاع قيمة تتكون من أربعة رموز: الرمز الأول هو الحرف الأول الذي تبدأ به الكلمة التي تم تمريرها إلى الدالة، والرموز الثلاثة المتبقية هي أرقام تقيس طريقة اللفظ. يفترض عند تمرير كلمتين مشابهتين في اللفظ أن تقوم الدالة SOUNDEX بإرجاع نفس القيمة لكلتا الكلمتين، على سبيل المثال عند تمرير القيمة Muhamed إلى الدالة فإن الناتج هو القيمة M530 ، أيضا عن تمرير الكلمة Mohammed فإن الناتج هو القيمة M530 .  قم بتنفيذ جملة الاستعلام التالية لتشاهد النتيجة بنفسك لمجموعة من الكلمات التي تشابه كلمة Muhamed في اللفظ:

SELECT soundex('Mohammed')AS Mohammed_column,soundex('Muhamed') AS Muhamed, soundex('Muhamad')AS Muhamad, soundex('Muhammed') AS Muhammed, soundex('Mouhamed')AS Mouhamed;

النتيجة هي:

توجد أيضا دالة أخرى لها علاقة بالدالة السابقة وهي دالة DIFFERENCE ، تستقبل هذه الدالة مدخلان وهما التعبيران النصيان المطلوب معرفة الفرق بينهما في طريقة اللفظ، وتعيد قيمة عددية صحيحة محصورة بين 0 و 4 وتمثل مقدار الفرق بين الكلمتين في طريقة اللفظ، بحيث لو كانت القيمة العائدة هي 4 فهذا يعني أن الكلمتين متشابتهان جداً، وإذا كانت 3 فهذا يعني وجود تشابه ولكنه أقل من الحالة السابقة وهلم جراً مع القيم 2 و 1 ، وإذا كانت القيمة العائدة صفر فهذا يعني عدم وجود تشابه في اللفظ على الإطلاق. استخدام دالة DIFFERENCE سيكون مفيد لصياغة استعلام يبحث في جدول عن نتائج تتشابه في اللفظ مع كلمة معينة، وقبل صياغة هذا الاستعلام بإمكانك تجربة الاستعلامات التالية لتتعرف على دالة DIFFERENCE وترى المخرجات التي ستظهر عند مقارنة كلمة Muhamed مع كلمات أخرى:

SELECT DIFFERENCE('Muhamed','Mohammed'); -- Output: 4

SELECT DIFFERENCE('Muhamed', 'Mohame');   -- Output: 3

SELECT DIFFERENCE('Muhamed', 'Microsoft'); -- Output: 1

الآن سنقوم باستخدام الدالة DIFFERENCE  للبحث في جدول النزلاء Guests عن النزلاء الذين تشابه أسمائهم الأولى الاسم Muhamed  على البيانات التالية:

الاستعلام كالآتي:

SELECT * FROM Guests WHERE DIFFERENCE(FirstName, 'Muhamed')= 4;

والنتيجة هي:

وهي الكلمات المشابهة جداً لكلمة Muhamed. إذا أردت إظهار مزيد من النتائج على حساب دقة البحث فيمكنك تحديد الفرق المطلوب ليكون 4 أو 3 وليس 4 فقط ، سيكون الاستعلام كما يلي:

SELECT * FROM Guests WHERE DIFFERENCE(FirstName, 'Muhamed')>= 3;

رأينا أن دالة Soundex وأختها الدالة Difference تعملان بشكل رائع مع اللغة الإنجليزية، السؤال الآن هل تدعم دالة soundex اللغة العربية؟ للأسف لا تدعم دالة soundex اللغة العربية، ولكن بإمكاننا توفير حلول بديلة. أثناء تسكعي في الانترنت وجدت المقالة التالية التي تتحدث عن خوارزمية  Soundex http://www.codeproject.com/Articles/26880/Arabic-Soundex ، وقد قام كاتب المقالة بعد فهم طريقة عمل الخوارزمية بتعديل سلوكها لكي تعمل مع اللغة العربية، ما سأقوم به الآن هو توفير الكود البرمجي الذي كتبه المؤلف بلغة C# ليكون دالة يمكن استخدامها ضمن استعلامات قواعد بيانات SQL Server. مع ملاحظة أني قمت بتعديل بسيط على الكود الموجود بالمقالة بحيث أصبح شكل الدالة كالآتي:

        public static string SoundexAr(string word)

        {

            int length = 4;

            // Value to return

            string value = "";

            try

            {

                switch (word[0])

                {

                    case 'ا':

                    case 'أ':

                    case 'إ':

                    case 'آ':

                        {

                            word = word.Substring(1, word.Length - 1);

                        }

                        break;

                }

                // Size of the word to process

                int size = word.Length;

                // Make sure the word is at least two characters in length

                if (size > 1)

                {

                    // Convert the word to character array for faster processing

                    char[] chars = word.ToCharArray();

                    // Buffer to build up with character codes

                    StringBuilder buffer = new StringBuilder();

                    buffer.Length = 0;

                    // The current and previous character codes

                    int prevCode = 0;

                    int currCode = 0;

                    // Ignore first character and replace it with fixed value

                    buffer.Append('x');

                    // Loop through all the characters and convert them to the proper character code

                    for (int i = 1; i < size; i++)

                    {

                        switch (chars[i])

                        {

                            case 'ا':

                            case 'أ':

                            case 'إ':

                            case 'آ':

                            case 'ح':

                            case 'خ':

                            case 'ه':

                            case 'ع':

                            case 'غ':

                            case 'ش':

                            case 'و':

                            case 'ي':

                                currCode = 0;

                                break;

                            case 'ف':

                            case 'ب':

                                currCode = 1;

                                break;

                            case 'ج':

                            case 'ز':

                            case 'س':

                            case 'ص':

                            case 'ظ':

                            case 'ق':

                            case 'ك':

                                currCode = 2;

                                break;

                            case 'ت':

                            case 'ث':

                            case 'د':

                            case 'ذ':

                            case 'ض':

                            case 'ط':

                                currCode = 3;

                                break;

                            case 'ل':

                                currCode = 4;

                                break;

                            case 'م':

                            case 'ن':

                                currCode = 5;

                                break;

                            case 'ر':

                                currCode = 6;

                                break;

                        }

                        // Check to see if the current code is the same as the last one

                        if (currCode != prevCode)

                        {

                            // Check to see if the current code is 0 (a vowel); do not process vowels

                            if (currCode != 0)

                                buffer.Append(currCode);

                        }

                        // Set the new previous character code

                        prevCode = currCode;

                        // If the buffer size meets the length limit, then exit the loop

                        if (buffer.Length == length)

                            break;

                    }

                    // Pad the buffer, if required

                    size = buffer.Length;

                    if (size < length)

                        buffer.Append('0', (length - size));

                    // Set the value to return

                    value = buffer.ToString();

                }

                // Return the value

                return value;

            }

            catch

            {

                return "0000";

            }

        }

كل ما تبقى عليك الآن هو تحويل دالة C# السابقة إلى دالة SQL Server ، لمعرفة طريقة عمل ذلك يرجى قراءة مقالتي "تحويل الدوال المكتوبة بلغة C# إلى دوال SQL Server"، وبعد القيام بالخطوة السابقة قم بإضافة المرجع الذي أنشأته إلى قاعدة البيانات، ومن ثم قم بإنشاء دالة SQL Server تستخدم المرجع السابق، يمكنك عمل ذلك عبر تنفيذ الاستعلام التالي على قاعدة البيانات مع تغيير ما يلزم في الكود – مثل مسار المرجع - :

-- Install Assembly

CREATE ASSEMBLY MyHotelAssembly FROM 'C:\MyHotelDatabaseProject1.dll'

GO

-- Create SoundexAr Function

CREATE FUNCTION [dbo].[SoundexAr](@word nvarchar(max))

RETURNS nvarchar(4)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME MyHotelAssembly.UserDefinedFunctions.SoundexAr;

GO

الآن يمكنك الاستمتاع بمنافع دالة Soundex للغة العربية، قم بتجربة الاستعلام التالي لترى النتيجة:

SELECT dbo.SoundexAr(N'احمد'), dbo.SoundexAr(N'أحمد'), dbo.SoundexAr(N'أحميد'), dbo.SoundexAr(N'احمدد');

بذلك أكون قد انتهيت من الحديث عن دالة soundex وبقي أن أشير إلى أن دالة soundex ليست دالة مثالية ولا تعمل في جميع الحالات بنسبة 100%، هي دالة جيدة وتؤدي الغرض ولكن لها بعض الحدود التي لا تجعلها دالة مثالية للتعرف على الكلمات المتشابهة في اللفظ، من تلك الحدود على سبيل المثال أنها سوف تفشل في التعرف على الكلمات المتشابهة في اللفظ ولكنها مختلفة في تهجئة الكتابة، على سبيل المثال كلمة photo وكلمة foto كلمتان متشابهتان في اللفظ ومختلفتان في تهجئة الكتابة، لذلك لن تستطيع دالة soundex إخراج نفس القيمة لكلتا الكلمتين، فنتيجة تمرير الكلمة photo للدالة soundex هي P300 ، بينما نتيجة foto هي F300 ، أيضا من حدود هذه الدالة أنها لا تستطيع التعرف على الكلمات التي تحوي رموز أو مسافة، على سبيل المثال، اسمي الأخير في جواز سفري مدون كما يلي AL-MAWSAMI – وحاله هو حال معظم أسماء العائلات العربية التي تبدأ بـ(أل) التعريف – ، فلو قام موظف استقبال الفندق بتسجيله بهذه الطريقة، ثم جاء شخص غريب يريد اغتيالي في الفندق، فسأل موظف الاستقبال عن شخص اسمه الأخير هو ALMAWSAMI ولم يخبر الموظف عن إضافة علامة الشرطة بعد AL ، وقام موظف الاستقبال بالاستعلام عن ذاك الشخص، فإنه لن يجد شيئاً في نتائج البحث، وبذلك أكون قد نجوت من الموت المحقق بسبب سلوك دالة soundex ، والسبب أن النتيجة التي تعيدها الدالة مع الحالة الأولى AL-MAWSAMI هي A400 ، ولكنها مع الحالة الثانية ALMAWSAMI ستكون A452 ، وتفسير ذلك أن دالة soundex قامت بقراءة القيمة الممررة لها إلى أن وجدت رمز أو مسافة فتوقفت عندها وقامت بإرجاع القيمة التي تخص النص الذي يسبق الرمز أو المسافة، وبذلك فإن القيمة العائدة لكلمة AL-MAWSAMI هي نفسها القيمة العائدة لكلمة AL !

SELECT soundex('almawsami'); -- Output: A452

SELECT soundex('al mawsami'); -- Output: A400

SELECT soundex('al-mawsami'); -- Output: A400

SELECT soundex('al'); -- Output: A400