آموزش LIKE و کاراکترهای جانشین در SQL اکسس با مثال عملی

جستجو با علامت مساوی زمانی خوب است که مقدار دقیق را بدانیم؛ اما کاربر معمولاً فقط آغاز نام مشتری، بخشی از عنوان کالا یا چند رقم از کد را به خاطر دارد. دستور LIKE در SQL اکسس امکان می‌دهد این نشانه‌های ناقص را به الگوی مشخص تبدیل کنیم و نتایج را بدون پیمایش دستی تمام رکوردها به دست آوریم.

در این درس رفتار موتور پایگاه داده اکسس را بررسی می‌کنیم، نه صرفاً دستورهای عمومی SQL را. نمادهای متداول ANSI-89، تفاوت آن‌ها با تنظیم ANSI-92، جستجوی کاراکترهای واقعی و انتقال امن متن از VBA به QueryDef آموزش داده می‌شود تا خروجی جستجو قابل پیش‌بینی باشد.

مفهوم و محدوده کاربرد

LIKE مقدار یک فیلد متنی را با یک الگو مقایسه می‌کند. در حالت رایج ANSI-89 اکسس، ستاره به معنی صفر یا چند کاراکتر، علامت سؤال به معنی یک کاراکتر و علامت # به معنی یک رقم است. کروشه نیز می‌تواند مجموعه یا بازه‌ای از کاراکترهای مجاز را نشان دهد. وجود هر نماد باید بر اساس نیاز واقعی جستجو باشد.

الگوی “عل*” نام‌هایی را انتخاب می‌کند که با «عل» آغاز می‌شوند؛ اما “*عل*” همین دنباله را در هر جای مقدار می‌پذیرد. این دو نتیجه یکسان نیستند. برای مثال نام «علی» در هر دو حالت می‌آید، ولی نامی که دنباله موردنظر را در میانه دارد فقط در حالت دوم می‌آید. قبل از طراحی فرم باید روشن کنیم کاربر کدام نوع تطبیق را می‌خواهد.

اگر پایگاه داده برای ANSI-92 تنظیم شده باشد، معمولاً درصد به جای ستاره و زیرخط به جای علامت سؤال به کار می‌رود. نباید در یک کوئری نمادهای دو حالت را مخلوط کنیم. تنظیم دیتابیس و نوع اتصال، از جمله DAO یا OLE DB، روی تفسیر الگو اثر می‌گذارند؛ الگوی موفق در یک محیط لزوماً در همه محیط‌ها معنی یکسان ندارد.

داده‌های نمونه و فرض‌ها

جدول Customers دارای CustomerID، CustomerName و Email و جدول Products دارای ProductID، ProductCode و ProductName فرض می‌شود. نام‌های «علی تجارت»، «علوی»، «نیلوفر» و «بنی‌اعتماد» را وارد کنید تا تفاوت جستجوی آغاز و میانه روشن شود. یک کد دارای صفر ابتدایی و یک نام دارای ستاره واقعی هم به داده آزمایشی بیفزایید.

ساختار دستور و تفسیر آن

الگو بعد از LIKE و داخل WHERE نوشته می‌شود. در SQL View اکسس، متن را می‌توان در کوتیشن مناسب قرار داد؛ اما نوع کوتیشن، مجموعه Wildcard را تغییر نمی‌دهد. نمونه اول بر اساس حالت پیش‌فرض ANSI-89 نوشته شده است. در فیلدهای متنی دارای فاصله یا نام رزروشده، نام فیلد را در کروشه قرار دهید.

اگر متن جستجو از فرم یا ورودی کاربر می‌آید، آن را به SQL خام نچسبانید. ابتدا مشخص کنید کاربر متن معمولی وارد می‌کند یا یک الگوی پیشرفته؛ سپس مقدار را با پارامتر نوع‌دار به QueryDef بدهید. ستاره را فقط هنگامی به پارامتر اضافه کنید که حالت جستجو واقعاً «شروع با» یا «شامل» باشد.

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE "Al*"
ORDER BY CustomerName, CustomerID;

مثال‌های گام‌به‌گام

سه مثال بعدی از جستجوی آغاز نام به بررسی ساختار کد محصول و در نهایت یافتن ستاره واقعی می‌رسند. در هر مثال ببینید کدام قسمت شرط قطعی است و کدام قسمت می‌تواند تغییر کند؛ همین تفاوت مبنای طراحی جستجوهای کاربردی خواهد بود.

تطبیق ابتدای نام

ستاره هر دنباله‌ای از کاراکترها، حتی دنباله خالی، را مجاز می‌کند. بنابراین نام‌هایی که با Al شروع می‌شوند انتخاب خواهند شد. برای جستجوی قابل استفاده مجدد، متن ثابت را به پارامتر تبدیل کنید و سیاست ورودی خالی را از ابتدا تعیین کنید.

SELECT CustomerID, CustomerName FROM Customers WHERE CustomerName LIKE "Al*";

الگوی موقعیت و رقم

نماد # فقط یک رقم را در جایگاه آخر می‌پذیرد؛ بنابراین A120 و A121 نتیجه می‌شوند اما A12X نه. اگر کد دارای کاراکتر و صفر ابتدایی است، Short Text گزینه مناسب‌تری است. این الگو جستجو را محدود می‌کند ولی جای قانون اعتبارسنجی فیلد را نمی‌گیرد.

SELECT ProductID, ProductCode FROM Products WHERE ProductCode LIKE "A12#";

جستجوی ستاره واقعی

ستاره داخل کروشه به‌صورت نویسه واقعی تفسیر می‌شود و دو ستاره بیرونی هر متن دلخواه را می‌پذیرند. این حالت برای نام کالا یا کدی مفید است که خودش علامت ستاره دارد. پیش از استفاده در فرم، حتماً مجموعه کاراکترهای جانشین پایگاه را بررسی کنید.

SELECT ProductID, ProductName FROM Products WHERE ProductName LIKE "*[*]*";

اجرای کوئری با VBA در اکسس

در کد VBA یک QueryDef موقت ساخته می‌شود و مقدار پیشوند از طریق پارامتر به آن اختصاص می‌یابد. اگر نام مشتری دارای علامت نقل‌قول یا آپاستروف باشد، این مقدار همچنان داده محسوب می‌شود و ساختار دستور را نمی‌شکند. نتیجه با DAO Recordset خوانده و شناسه و نام مشتری در پنجره Immediate چاپ می‌شود.

این روال در محیط VBA خود Microsoft Access و روی جدول Customers اجرا می‌شود. در صورت نیاز ارجاع DAO را در پروژه بررسی کنید. دقت کنید پیشوند خالی به الگوی تنها ستاره تبدیل می‌شود و همه نام‌های غیر Null را برمی‌گرداند؛ برای فرم عمومی بهتر است کمینه طول ورودی تعیین شود و در صورت خالی بودن، پیام مناسب نمایش داده شود.

Option Explicit
Public Sub FindCustomersByPrefix()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    On Error GoTo Fail
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", _
        "PARAMETERS [pPrefix] Text (100); " & _
        "SELECT CustomerID, CustomerName FROM Customers " & _
        "WHERE CustomerName LIKE [pPrefix] & '*' " & _
        "ORDER BY CustomerName, CustomerID;")
    qdf.Parameters("pPrefix") = "Al"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    Do Until rs.EOF
        Debug.Print rs!CustomerID, rs!CustomerName
        rs.MoveNext
    Loop
CleanUp:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
Fail:
    Debug.Print Err.Number, Err.Description
    Resume CleanUp
End Sub

کنترل نتیجه و شرایط مرزی

ابتدا پیشوندی را آزمایش کنید که دو مشتری پیدا کند و سپس پیشوندی را وارد کنید که هیچ نتیجه‌ای ندارد. یک CustomerName از نوع Null، یک نام دارای ستاره و یک نام دارای آپاستروف نیز اضافه کنید. تفاوت خروجی الگوی آغاز با الگوی شامل را یادداشت کنید؛ اگر تفاوتی نمی‌بینید، داده نمونه را دقیق‌تر انتخاب کنید.

خطاهای رایج و راه‌حل

الگوی ظاهراً درست ممکن است به دلیل تنظیم نادرست Wildcard یا نوع فیلد، هیچ نتیجه‌ای ندهد یا رکوردهای زیادی برگرداند. نخست این موارد را کنترل کنید:

بهترین روش‌ها برای کوئری قابل اعتماد

اگر کاربر آغاز نام را می‌داند، جستجوی پیشوندی را بر جستجوی شامل ترجیح دهید. بسته به ایندکس، حجم داده و نوع اتصال، الگوی پیشوندی ممکن است کم‌هزینه‌تر باشد؛ اما سرعت واقعی را باید اندازه گرفت و نمی‌توان درباره تمام کوئری‌ها حکم قطعی داد.

کد کالا و شناسه‌ای که صفر ابتدایی یا حروف دارد را معمولاً در فیلد متنی ذخیره کنید. نگهداری کد 00123 در فیلد عددی صفرهای ابتدایی را از بین می‌برد. LIKE برای یافتن کد به کار می‌رود و جایگزین Validation Rule برای جلوگیری از ورود کد نامعتبر نیست.

در فرم جستجو نوع تطبیق را به کاربر اعلام کنید: «شروع با»، «شامل»، «تطبیق دقیق» یا «الگوی کد». تغییر پنهانی این حالت‌ها باعث می‌شود کاربر نتیجه را اشتباه تفسیر کند. نام QueryDef یا پارامترها باید این انتخاب را مشخص کنند تا هنگام نگهداری پروژه علت خروجی قابل فهم باشد.

الگوی دارای ستاره در آغاز معمولاً دامنه گسترده‌تری را بررسی می‌کند و احتمال کندشدن دارد. قبل از ایجاد ایندکس اضافی، تعداد رکوردها، اندازه فیلد و میزان انتخابی بودن شرط را بسنجید. همچنین در بعضی شیوه‌های اتصال مثل OLE DB ممکن است نمادهای ANSI-92 کاربرد داشته باشند، در حالی که DAO از الگوی متداول اکسس استفاده می‌کند.

اگر نتیجه برای نمایش در فهرست مشتریان است، ORDER BY و یک کلید مانند CustomerID را برای شکست تساوی اضافه کنید. LIKE فقط تعیین می‌کند چه رکوردی عضو نتیجه باشد؛ ترتیب نمایش را تضمین نمی‌کند. ترتیب اتفاقی ورود رکوردها یا نمایش قبلی Datasheet مبنای قابل اعتماد گزارش نیست.

آزمایشگاه تمرین

فرمی با txtPrefix و دکمه جستجو بسازید. ابتدا شرط کنید دست‌کم دو کاراکتر وارد شده باشد و تعداد نتیجه‌ها را نشان دهید. سپس دکمه دوم را برای حالت «شامل عبارت» ایجاد و خروجی دو حالت را با هم مقایسه کنید. برای نگهداری بهتر، منطق هر حالت را در QueryDef روشن و جداگانه قرار دهید.

برای تمرین دشوارتر، کدهای A120 و A121 و B120 و A12X را وارد کنید. الگوهایی برای شروع با A، پایان با یک رقم و وجود یک کاراکتر ناشناخته در جای مشخص بنویسید. پیش از اجرا، جدولی از جواب مورد انتظار تهیه کنید. اگر خواستید ANSI-92 را بیازمایید، در یک نسخه آزمایشی همه نمادها را هماهنگ تبدیل کنید.

درس‌های مرتبط

درس WHERE جایگاه شرط را توضیح می‌دهد؛ درس QueryDef روش استفاده از پارامتر را روشن می‌کند و ORDER BY به ترتیب خروجی می‌پردازد. این سه موضوع همراه LIKE یک قابلیت جستجوی قابل نگهداری می‌سازند.

• کاربرد WHERE در SQL اکسس: آموزش SQL در Microsoft Access: کاربرد جزء WHERE در SQL
• کوئری پارامتری با QueryDef: کوئری پارامتری در SQL اکسس با QueryDef و VBA
• مرتب‌سازی ORDER BY در اکسس: آموزش مرتب‌سازی نتایج کوئری با ORDER BY در SQL اکسس

جمع‌بندی

دستور LIKE در SQL اکسس زمانی دقیق است که حالت تطبیق و نوع Wildcard روشن باشند. نوع جستجو را مشخص کنید، تنظیم ANSI پایگاه را بشناسید، ورودی متغیر را پارامتری ارسال کنید و رکوردهای مورد انتظار و غیرمنتظره را بررسی نمایید.

کیفیت جستجو فقط به بازشدن Datasheet وابسته نیست؛ تعداد نتایج اشتباه و مواردی که به‌اشتباه حذف شده‌اند نیز اهمیت دارند. پس از اطمینان از منطق، عملکرد کوئری را با حجم واقعی داده بسنجید و الگو را برای توسعه‌دهنده بعدی مستند کنید.