فیلتر دقیق حروف الفبا در TSQL از طریق CLR Regex

فیلتر دقیق حروف الفبا در T-SQL از طریق CLR Regex

محدودیت‌های عملگر LIKE در T-SQL برای فیلتر کردن دقیق رشته‌ها، به ویژه هنگام کار با الگوهای پیچیده و حساس به حروف، چالش‌برانگیز هستند. برای مثال، فیلتر کردن نام‌هایی که فقط شامل حروف الفبا باشند یا با یک حرف خاص شروع شوند، با LIKE ممکن است دشوار و ناکارآمد باشد. این مقاله راه حلی قدرتمند برای این چالش ارائه می‌دهد: استفاده از عبارات منظم (Regular Expressions) از طریق CLR در SQL Server، که امکان فیلتر کردن دقیق و پیچیده الفبایی را فراهم می‌کند.

عبارات منظم ابزاری قدرتمند برای جستجو و دستکاری الگوهای متنی هستند. اگرچه T-SQL به طور بومی از عبارات منظم پشتیبانی نمی‌کند، می‌توان با استفاده از قابلیت CLR در SQL Server، از قابلیت‌های RegEx در دات‌نت (.NET) بهره برد. این رویکرد به شما امکان می‌دهد تا توابع SQL ایجاد کنید که از عبارات منظم برای فیلتر کردن داده‌های متنی استفاده کنند.

برای پیاده‌سازی این راه‌حل، ابتدا باید یک اسمبلی CLR ایجاد کرده و آن را در SQL Server رجیستر کنید. این اسمبلی شامل یک تابع ساده خواهد بود که یک رشته ورودی و یک الگوی عبارت منظم را دریافت می‌کند و بررسی می‌کند که آیا رشته با الگو مطابقت دارد یا خیر. مراحل زیر نحوه انجام این کار را توضیح می‌دهند:

۱. ایجاد یک تابع CLR Regex

ابتدا، کدهای C# را برای تابع RegEx خود ایجاد کنید. این تابع یک رشته ورودی و یک الگوی RegEx را می‌گیرد و یک مقدار بولی (true/false) را برمی‌گرداند که نشان‌دهنده مطابقت است.


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegExMatch(SqlString pattern, SqlString input)
    {
        if (pattern.IsNull || input.IsNull)
            return SqlBoolean.Null;

        return new SqlBoolean(Regex.IsMatch(input.Value, pattern.Value));
    }
}

این کد یک تابع CLR به نام RegExMatch ایجاد می‌کند که از کلاس Regex.IsMatch در دات‌نت برای بررسی مطابقت استفاده می‌کند. ویژگی IsDeterministic = true به SQL Server اجازه می‌دهد تا نتایج این تابع را کش کند، که می‌تواند عملکرد را بهبود بخشد.

۲. کامپایل و رجیستر کردن اسمبلی در SQL Server

پس از ایجاد کد C#، آن را کامپایل کنید تا یک فایل DLL (مثلاً RegExFunctions.dll) تولید شود. سپس، مراحل زیر را برای فعال‌سازی CLR و رجیستر کردن اسمبلی و تابع در SQL Server دنبال کنید:

ابتدا، گزینه ‘clr enabled’ را فعال کنید (اگر از قبل فعال نیست):


EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

در مرحله بعد، اسمبلی را در SQL Server ایجاد کنید و سپس تابع CLR را ثبت نمایید:


CREATE ASSEMBLY RegExFunctions
FROM 'C:\YourPathToYourDLL\RegExFunctions.dll' -- مسیر فایل DLL خود را اینجا قرار دهید
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION RegExMatch
(
    @pattern NVARCHAR(4000),
    @input NVARCHAR(4000)
)
RETURNS BIT
EXTERNAL NAME RegExFunctions.[UserDefinedFunctions].RegExMatch;
GO

این دستورات، اسمبلی DLL شما را در SQL Server بارگذاری می‌کنند و یک تابع T-SQL به نام RegExMatch ایجاد می‌کنند که به تابع C# شما اشاره دارد. (نکته: در نسخه‌های جدید SQL Server، ممکن است نیاز به فعال‌سازی TRUSTWORTHY یا امضای اسمبلی داشته باشید تا از خطاهای امنیتی جلوگیری کنید).

۳. استفاده از تابع RegExMatch برای فیلتر کردن داده‌ها

اکنون که تابع RegExMatch را آماده دارید، می‌توانید از آن برای فیلتر کردن پیشرفته داده‌ها استفاده کنید. در اینجا چند مثال از کاربرد آن برای فیلتر کردن حروف الفبا آورده شده است:

فیلتر بر اساس حروف شروع کننده

فرض کنید می‌خواهید نام‌هایی را پیدا کنید که با حروف ‘A’ تا ‘E’ شروع می‌شوند. از الگوی عبارت منظم زیر استفاده کنید:

'^[A-E].*$'

این الگو به این معناست که رشته باید با هر حرفی از ‘A’ تا ‘E’ شروع شود (^[A-E]) و به دنبال آن صفر یا چند کاراکتر دیگر (.*) باشد تا پایان رشته ($).


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[A-E].*$', Name) = 1;

اگر می‌خواهید نام‌هایی را فیلتر کنید که با حروف ‘A’ تا ‘L’ شروع و به حروف ‘A’ تا ‘L’ ختم می‌شوند، از این الگو استفاده کنید:

'^[A-L].*[A-L]$'

این الگو تضمین می‌کند که رشته با یک حرف از ‘A’ تا ‘L’ شروع شده و با همان محدوده حروف نیز به پایان برسد.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[A-L].*[A-L]$', Name) = 1;

فیلتر بر اساس محتوای فقط حروف الفبا

برای یافتن نام‌هایی که فقط شامل حروف الفبا هستند (بدون اعداد یا کاراکترهای خاص)، الگوی زیر بسیار مفید است:

'^[a-zA-Z]*$'

این الگو به این معنی است که رشته از ابتدا تا انتها فقط باید شامل حروف کوچک (a-z) یا بزرگ (A-Z) باشد.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[a-zA-Z]*$', Name) = 1;

اگر می‌خواهید نام‌هایی را فیلتر کنید که فقط شامل حروف الفبا باشند و حداقل یک کاراکتر داشته باشند، از الگوی زیر استفاده کنید:

'^[a-zA-Z]+$'

تفاوت * و + در این است که * به معنای “صفر یا بیشتر” است، در حالی که + به معنای “یک یا بیشتر” است.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[a-zA-Z]+$', Name) = 1;

فیلتر کردن نام‌هایی که فقط شامل حروف کوچک یا بزرگ هستند

برای یافتن نام‌هایی که فقط شامل حروف کوچک هستند:

'^[a-z]*$'

این الگو مطمئن می‌شود که تمامی کاراکترها در رشته فقط از نوع حروف کوچک انگلیسی هستند.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[a-z]*$', Name) = 1;

و برای نام‌هایی که فقط شامل حروف بزرگ هستند:

'^[A-Z]*$'

این الگو نیز مشابه قبلی، اما برای حروف بزرگ انگلیسی کاربرد دارد.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[A-Z]*$', Name) = 1;

فیلتر بر اساس عدم وجود اعداد یا کاراکترهای خاص

اگر می‌خواهید نام‌هایی را فیلتر کنید که هیچ عددی در آن‌ها وجود ندارد، می‌توانید از الگوی نفی (negation) استفاده کنید:

'^[^0-9]*$'

این الگو به این معنی است که رشته نباید حاوی هیچ رقمی (0-9) باشد و ^ داخل براکت به معنای نفی است.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[^0-9]*$', Name) = 1;

برای فیلتر کردن نام‌هایی که هیچ کاراکتر خاصی (مانند @ # $ %) ندارند:

'^[^@#$%]*$'

این الگو مطمئن می‌شود که کاراکترهای مشخص شده در بین [] و با پیشوند ^ در رشته وجود نداشته باشند.


SELECT Name
FROM Employees
WHERE dbo.RegExMatch('^[^@#$%]*$', Name) = 1;

نکات عملکردی و بهینه‌سازی

استفاده از توابع CLR می‌تواند عملکرد خوبی داشته باشد، اما باید با دقت مورد استفاده قرار گیرد. برای فیلتر کردن حجم زیادی از داده‌ها، ممکن است نیاز به بررسی تأثیر آن بر عملکرد کلی سیستم داشته باشید. افزودن توابع CLR می‌تواند یک هزینه عملکردی جزئی داشته باشد، اما در موارد خاصی که LIKE به اندازه کافی قدرتمند نیست، مزایای آن بیشتر از معایبش است.

برای بهبود عملکرد، مطمئن شوید که تابع CLR شما IsDeterministic = true است، که به SQL Server اجازه می‌دهد تا نتایج را کش کند. همچنین، تا حد امکان، عبارت منظم را قبل از اعمال آن بر روی یک مجموعه بزرگ از داده‌ها، دقیق و بهینه طراحی کنید.

فیلتر دقیق حروف الفبا در TSQL از طریق CLR Regex