PATINDEX SQL Server جستجوی الگو با Wildcards

PATINDEX در SQL Server: راهنمای جامع جستجوی الگو با عبارات منظم

تابع PATINDEX در SQL Server ابزاری قدرتمند برای یافتن موقعیت شروع اولین رخداد یک الگو در یک رشته است. این تابع به شما امکان می‌دهد تا با استفاده از کاراکترهای وایلدکارد و کلاس‌های کاراکتر، الگوهای پیچیده‌تری را نسبت به تابع LIKE جستجو کنید. درک صحیح نحوه عملکرد PATINDEX برای هر متخصص SQL و توسعه‌دهنده پایگاه داده که با جستجوی رشته‌ها سروکار دارد، حیاتی است. این تابع می‌تواند در تمیز کردن داده‌ها، اعتبارسنجی ورودی‌ها و تجزیه رشته‌های متنی بسیار مفید باشد و به شما کمک می‌کند تا عملیات جستجو را با دقت و کارایی بالا انجام دهید.

PATINDEX موقعیت شروع اولین رخداد یک الگو را در یک رشته مشخص برمی‌گرداند. اگر الگو پیدا نشود، تابع 0 را برمی‌گرداند. این تابع از سینتکسی مشابه تابع LIKE برای تعریف الگوها استفاده می‌کند، اما خروجی آن یک مقدار عددی است که این امکان را می‌دهد تا از آن در کنار توابع دستکاری رشته دیگر مانند SUBSTRING بهره ببرید.

سینتکس پایه برای استفاده از PATINDEX به شکل زیر است:

در این سینتکس، `pattern` عبارتی است که شامل کاراکترهای وایلدکارد می‌شود و باید جستجو شود، و `expression` رشته‌ای است که قرار است الگو در آن جستجو شود. هر دو آرگومان باید از نوع داده کاراکتری باشند.

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

`%`: نشان‌دهنده رشته‌ای از صفر یا بیشتر کاراکتر است.
`_`: نشان‌دهنده یک کاراکتر منفرد است.
`[ ]`: نشان‌دهنده هر کاراکتر منفرد در یک محدوده یا مجموعه مشخص شده است. برای مثال، `[a-z]` هر حرف کوچک انگلیسی، و `[abc]` هر یک از حروف a، b یا c را مشخص می‌کند.
`[^]`: نشان‌دهنده هر کاراکتر منفردی است که در محدوده یا مجموعه مشخص شده نیست. برای مثال، `[^0-9]` هر کاراکتری را مشخص می‌کند که رقم نباشد.

بیایید با چند مثال ساده شروع کنیم تا نحوه کار PATINDEX را بهتر درک کنیم. این مثال موقعیت شروع رشته ‘SQL’ را در عبارت ‘SQL Server Central’ پیدا می‌کند:

SELECT PATINDEX('%SQL%', 'SQL Server Central');

نتیجه 1 خواهد بود، زیرا ‘SQL’ از موقعیت اول شروع می‌شود.

در این مثال، موقعیت شروع ‘Server’ را پیدا می‌کنیم:

SELECT PATINDEX('%Server%', 'SQL Server Central');

نتیجه 5 خواهد بود.

حالا فرض کنید می‌خواهیم یک الگو را بدون استفاده از کاراکترهای وایلدکارد جستجو کنیم. در این حالت، PATINDEX دقیقاً مانند CHARINDEX عمل می‌کند:

SELECT PATINDEX('SQL', 'SQL Server Central');

باز هم، نتیجه 1 خواهد بود. اما اگر الگو یافت نشود، PATINDEX صفر را برمی‌گرداند:

SELECT PATINDEX('%Microsoft%', 'SQL Server Central');

نتیجه 0 خواهد بود.

یکی از کاربردهای اصلی PATINDEX در ترکیب با SUBSTRING است. این ترکیب به شما امکان می‌دهد تا بخشی از یک رشته را بر اساس یک الگو استخراج کنید. به عنوان مثال، برای استخراج نام دامین از یک آدرس ایمیل:

DECLARE @email VARCHAR(100) = 'user@example.com';
SELECT SUBSTRING(@email, PATINDEX('%@%', @email) + 1, LEN(@email) - PATINDEX('%@%', @email));

این عبارت ابتدا موقعیت ‘@’ را پیدا کرده و سپس با استفاده از SUBSTRING، قسمت دامین را استخراج می‌کند.

استفاده از کاراکترهای وایلدکارد پیشرفته

کاراکترهای وایلدکارد `[]` و `[^]` به PATINDEX قدرت بیشتری می‌بخشند. فرض کنید می‌خواهیم هر کاراکتری را که عدد نیست، پیدا کنیم. این می‌تواند برای اعتبارسنجی رشته‌های فقط عددی مفید باشد:

SELECT PATINDEX('%[^0-9]%', '123456789'); -- خروجی: 0 (همه اعداد هستند)
SELECT PATINDEX('%[^0-9]%', '123a456');   -- خروجی: 4 (اولین کاراکتر غیر عددی 'a' است)

این قابلیت به شما کمک می‌کند تا به سرعت تشخیص دهید که آیا یک رشته فقط شامل اعداد است یا خیر.

یافتن کاراکترهای خاص: فرض کنید می‌خواهید موقعیت شروع هر کاراکتر خاصی را پیدا کنید، مثلاً حروف کوچک:

SELECT PATINDEX('%[a-z]%', 'THIS IS A TEST'); -- خروجی: 0 (فقط حروف بزرگ)
SELECT PATINDEX('%[a-z]%', 'This Is A Test'); -- خروجی: 2 (حرف 'h' کوچک است)

Escaping Wildcards

اگر بخواهید یک کاراکتر وایلدکارد را به عنوان یک کاراکتر عادی جستجو کنید (مثلاً خود `%` یا `_` را جستجو کنید)، باید از عبارت `ESCAPE` استفاده کنید. `ESCAPE` به PATINDEX می‌گوید که کاراکتر بعدی پس از کاراکتر `ESCAPE` نباید به عنوان وایلدکارد تفسیر شود.

مثال: جستجوی رشته‌ای که شامل ‘%` است. در اینجا از `\` به عنوان کاراکتر `ESCAPE` استفاده می‌کنیم:

SELECT PATINDEX('%100\%%', 'قیمت 100% تخفیف'); -- خروجی: 6 (موقعیت '100%')

در این مثال، `\` قبل از `%` به PATINDEX می‌گوید که `%` را به عنوان یک کاراکتر عادی (Literal) در نظر بگیرد، نه به عنوان یک وایلدکارد.

عملکرد PATINDEX

PATINDEX مانند LIKE، نمی‌تواند از ایندکس‌های استاندارد برای بهبود عملکرد جستجو استفاده کند، مگر اینکه الگو با یک رشته ثابت شروع شود. یعنی اگر الگوی شما با `_` یا `[` یا `%` شروع شود، PATINDEX نمی‌تواند از ایندکس‌های B-tree استفاده کند و مجبور به اسکن کامل جدول (Full Table Scan) می‌شود. این موضوع می‌تواند بر کارایی کوئری‌ها در جداول بزرگ تأثیر منفی بگذارد.

اگر عملکرد برای شما حیاتی است، همیشه ابتدا الگوهای خود را با دقت طراحی کنید. اگر نیاز به جستجوی الگوهای پیچیده در ستون‌های بزرگ دارید، ممکن است بخواهید به راه‌حل‌های جایگزین مانند Full-Text Search (جستجوی تمام متن) در SQL Server فکر کنید که برای این نوع عملیات بهینه‌سازی شده است.

موارد استفاده عملی PATINDEX

1. اعتبارسنجی قالب‌ها:
شماره تلفن: فرض کنید می‌خواهید مطمئن شوید یک رشته فقط شامل اعداد و خط تیره است:

SELECT
    CASE
        WHEN PATINDEX('%[^0-9-]%', '0912-123-4567') = 0 THEN 'فرمت معتبر'
        ELSE 'فرمت نامعتبر'
    END AS شماره_تلفن_اعتبارسنجی;

SELECT
    CASE
        WHEN PATINDEX('%[^0-9-]%', '0912A123-4567') = 0 THEN 'فرمت معتبر'
        ELSE 'فرمت نامعتبر'
    END AS شماره_تلفن_اعتبارسنجی_۲;

کد پستی: بررسی کنید که یک کد پستی فقط شامل 10 رقم است:

SELECT
    CASE
        WHEN LEN('1234567890') = 10 AND PATINDEX('%[^0-9]%', '1234567890') = 0 THEN 'کد پستی معتبر'
        ELSE 'کد پستی نامعتبر'
    END AS کد_پستی_اعتبارسنجی;

2. یافتن کاراکترهای خاص در یک رشته:
فرض کنید می‌خواهید موقعیت اولین حرف بزرگ را در یک رشته پیدا کنید:

SELECT PATINDEX('%[A-Z]%', 'this is a Test');

نتیجه 11 خواهد بود.

3. استفاده برای جایگزینی یا حذف بخش‌هایی از رشته:
اگر بخواهید تمام اعداد را از یک رشته حذف کنید، می‌توانید PATINDEX را در یک حلقه یا تابع T-SQL با REPLACE ترکیب کنید:

DECLARE @str VARCHAR(100) = 'abc123def456ghi';
DECLARE @pos INT;

SET @pos = PATINDEX('%[0-9]%', @str);

WHILE @pos > 0
BEGIN
    SET @str = REPLACE(@str, SUBSTRING(@str, @pos, 1), '');
    SET @pos = PATINDEX('%[0-9]%', @str);
END

SELECT @str AS رشته_بدون_اعداد;

این مثال عملی، هر چند کمی پیچیده‌تر است، اما قدرت PATINDEX را در ترکیب با سایر توابع نشان می‌دهد.

4. جستجو برای الگوهای ایمیل یا URL:
PATINDEX می‌تواند برای یافتن شروع یک الگوی ایمیل اولیه یا URL مورد استفاده قرار گیرد. البته این الگوها به دلیل تنوع زیاد، معمولاً بسیار پیچیده هستند و PATINDEX ممکن است برای اعتبارسنجی کامل کافی نباشد، اما برای یافتن الگوهای ساده می‌تواند مفید باشد:

SELECT PATINDEX('%_@_%.com%', 'myname@example.com');

این الگو یک بررسی پایه برای ساختار `@` و `.` در یک آدرس ایمیل انجام می‌دهد.

نتیجه‌گیری

PATINDEX یک تابع کاربردی و قدرتمند در SQL Server برای جستجوی الگوها در رشته‌ها است. با درک صحیح کاراکترهای وایلدکارد و نحوه استفاده از آن‌ها، می‌توانید جستجوهای پیچیده و دقیقی را انجام دهید. با این حال، باید به محدودیت‌های عملکردی آن، به ویژه در مورد عدم استفاده از ایندکس‌ها، توجه داشته باشید. در مواردی که نیاز به عملکرد بالا در جستجوی متنی دارید، بررسی گزینه‌هایی مانند Full-Text Search ضروری است. با ترکیب PATINDEX با سایر توابع رشته‌ای مانند SUBSTRING و REPLACE، می‌توانید راه‌حل‌های بسیار منعطف و کارآمدی برای تجزیه، اعتبارسنجی و دستکاری رشته‌ها در پایگاه داده خود ایجاد کنید.