جستجوی قدرتمند الگو در SQL Server با تابع PATINDEX
تابع `PATINDEX` در SQL Server برای یافتن اولین وقوع یک الگو در یک رشته مشخص استفاده می‌شود و موقعیت شروع آن الگو را برمی‌گرداند. این تابع بسیار شبیه به تابع `CHARINDEX` است، با این تفاوت که `PATINDEX` به شما امکان می‌دهد از کاراکترهای وایلدکارد برای تعریف الگو استفاده کنید، در حالی که `CHARINDEX` یک رشته ثابت را جستجو می‌کند.
سینتکس تابع `PATINDEX` به این صورت است که ابتدا الگو و سپس عبارتی که باید جستجو شود، مشخص می‌شود:
PATINDEX ( '%pattern%' , expression )
در اینجا، `pattern` (الگو) عبارتی است که می‌خواهید جستجو کنید و می‌تواند شامل کاراکترهای وایلدکارد باشد، و `expression` (عبارت) رشته‌ای است که تابع `PATINDEX` در آن به دنبال الگو می‌گردد. نوع داده بازگشتی `PATINDEX` بستگی به نوع داده `expression` دارد. اگر `expression` از نوع `VARCHAR(MAX)` باشد، مقدار بازگشتی `BIGINT` خواهد بود، در غیر این صورت `INT` برگردانده می‌شود. اگر الگو یافت نشود، تابع `PATINDEX` مقدار صفر (0) را برمی‌گرداند.
برای مثال، فرض کنید می‌خواهیم موقعیت شروع کلمه “SQL” را در رشته ‘MS SQL Tips’ پیدا کنیم. این کار را می‌توانیم با استفاده از `PATINDEX` به شکل زیر انجام دهیم:
SELECT PATINDEX('%SQL%', 'MS SQL Tips') AS Position
نتیجه این کوئری 4 خواهد بود، زیرا “SQL” از موقعیت چهارم شروع می‌شود. اگر به دنبال کلمه “Tip” باشیم:
SELECT PATINDEX('%Tip%', 'MS SQL Tips') AS Position
این کوئری موقعیت 8 را برمی‌گرداند. اگر الگویی که جستجو می‌کنیم در رشته وجود نداشته باشد، `PATINDEX` مقدار 0 را برمی‌گرداند. به عنوان مثال، جستجوی “ABC” در همان رشته:
SELECT PATINDEX('%ABC%', 'MS SQL Tips') AS Position
نتیجه 0 خواهد بود.
یکی از قابلیت‌های مهم `PATINDEX` استفاده از کاراکترهای وایلدکارد است که به شما امکان می‌دهد الگوهای پیچیده‌تری را تعریف کنید. در اینجا، نحوه استفاده از این کاراکترها در `PATINDEX` و مثال‌های مربوط به آن‌ها را بررسی می‌کنیم.
* **جستجوی رشته‌ای که با “T” شروع می‌شود و با “S” پایان می‌یابد:**
برای یافتن موقعیت اولین کلمه‌ای که با ‘T’ شروع شده و با ‘S’ به پایان می‌رسد، می‌توانیم از وایلدکارد `%` استفاده کنیم که نشان‌دهنده صفر یا بیشتر کاراکتر است.
SELECT PATINDEX('%T%S%', 'MS SQL Tips') AS Position
در این مثال، نتیجه 8 خواهد بود زیرا “Tips” با ‘T’ شروع و با ‘S’ پایان می‌یابد و در موقعیت 8 قرار دارد.
* **جستجوی رشته با یک کاراکتر خاص:**
برای جستجوی یک رشته که شامل کاراکتر ‘i’ است، می‌توانیم از الگوی زیر استفاده کنیم:
SELECT PATINDEX('%i%', 'MS SQL Tips') AS Position
این کوئری نتیجه 9 را برمی‌گرداند، که اولین موقعیت ‘i’ در رشته است.
* **جستجوی رشته‌ای که شامل حداقل 3 کاراکتر عددی باشد:**
SELECT PATINDEX('%[0-9][0-9][0-9]%', '123 Test 456 Demo') AS Position
این کوئری موقعیت 1 را برمی‌گرداند، زیرا “123” اولین گروه سه رقمی است.
* **جستجوی رشته‌ای که با “T” شروع می‌شود و دارای حداقل 3 کاراکتر باشد:**
SELECT PATINDEX('T%', 'Test') AS Position
این کوئری نتیجه 1 را برمی‌گرداند.
* **جستجوی رشته‌ای که در آن کاراکتر دوم ‘S’ باشد:**
SELECT PATINDEX('_S%', 'MS SQL Tips') AS Position
علامت `_` (underscore) نشان‌دهنده یک کاراکتر واحد است. در این مثال، ‘S’ کاراکتر دوم کلمه “SQL” است که در موقعیت 4 قرار دارد، بنابراین نتیجه 5 خواهد بود (1 + 4).
* **جستجوی رشته‌ای که با ‘MS’ شروع نمی‌شود:**
کاراکتر `^` (caret) برای نفی یک الگو در یک مجموعه استفاده می‌شود.
SELECT PATINDEX('[^M]%', 'MS SQL Tips') AS Position
این کوئری موقعیت 1 را برمی‌گرداند زیرا ‘M’ اولین کاراکتر است و در مجموعه ‘M’ وجود دارد (یعنی نفی نشده است). اگر اولین کاراکتر ‘M’ نباشد، نتیجه صفر (0) خواهد بود.
* **جستجوی رشته‌ای که در آن کاراکتر دوم ‘S’ یا ‘Q’ باشد:**
SELECT PATINDEX('_[SQ]%', 'MS SQL Tips') AS Position
این کوئری نتیجه 5 را برمی‌گرداند، زیرا ‘S’ دومین کاراکتر از “SQL” است که در موقعیت 4 قرار دارد.
* **استفاده از `PATINDEX` در ستون‌های جدول:**
`PATINDEX` به خوبی با ستون‌های جدول کار می‌کند. مثلاً برای یافتن سطرها در جدول `HumanResources.Employee` که در ستون `JobTitle` دارای عنوان “Manager” هستند:
SELECT JobTitle
FROM HumanResources.Employee
WHERE PATINDEX('%Manager%', JobTitle) > 0
در اینجا لیستی از کاراکترهای وایلدکارد رایج که می‌توانید با `PATINDEX` استفاده کنید آورده شده است:
| کاراکتر وایلدکارد | توضیحات | مثال |
| :—————- | :—————————————————- | :——————————————— |
| **%** | هر رشته‌ای از صفر یا بیشتر کاراکترها | `’%SQL%’` (شامل SQL می‌شود) |
| **_** | هر کاراکتر تکی | `’_SQL%’` (دومین کاراکتر SQL است) |
| **[ ]** | هر کاراکتر تکی در محدوده یا مجموعه مشخص شده | `'[A-G]%’` (با A تا G شروع می‌شود) |
| **[^ ]** | هر کاراکتر تکی که در محدوده یا مجموعه مشخص شده نباشد | `'[^A-G]%’` (با کاراکتری به غیر از A تا G شروع می‌شود) |
`PATINDEX` مقادیر زیر را برمی‌گرداند:
* **موقعیت شروع الگو:** یک عدد صحیح (INT) یا BIGINT، بسته به نوع داده ستونی که در آن جستجو می‌شود.
* **0 (صفر):** اگر الگو در رشته یافت نشود.
* **NULL:** اگر رشته‌ای که جستجو می‌شود `NULL` باشد.
محدودیت اصلی تابع `PATINDEX` در مقایسه با عبارت‌های منظم (Regular Expressions) کامل این است که `PATINDEX` از مجموعه محدودی از کاراکترهای وایلدکارد استفاده می‌کند و قابلیت‌های پیشرفته عبارات منظم (مانند گروه‌بندی، تکرار، یا OR منطقی بین الگوها) را ندارد. برای الگوهای جستجوی پیچیده‌تر، ممکن است نیاز به ترکیب `PATINDEX` با توابع دیگر SQL یا استفاده از قابلیت‌های جستجوی تمام‌متن (Full-Text Search) SQL Server باشد. با این حال، برای اکثر نیازهای جستجوی الگو در SQL Server، `PATINDEX` ابزاری قدرتمند و کارآمد است.