The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query
قبل از مطالعه باید بگویم سطح مقاله مبتدی میباشد.
گاهی اوقات در زمان Migration یک دیتابیس شما با جداولی برخورد می‌نمایید که محتویات بعضی از فیلد هایشان ترکیبی از عدد و حروف میباشد، و شما برای انجام یکسری از عملیات نیاز دارید. که حروف را از اعداد متمایز نمایید، یا اینکه مکان اولین کاراکتر غیر عددی را بعد از هر عدد، بیابید. برای انجام چنین کاری می‌توان از تابعی به نام Patindex استفاده نمود.
تابع PATINDEX به شما امکان، مکان یابی یک یا چند حرف در بین رشته‌های متنی را می‌دهد.
Syntax تابع PATINDEX بصورت زیر میباشد:
PATINDEX ( '%pattern%' , expression )
تابع PATINDEX شامل دو آرگومان میباشد که هر کدام را به اختصار توضیح می‌دهیم:
1- آرگومان اولPattern نامگذاری شده است، Pattern در واقع یک الگوی اختصاصی میباشد که توسط کاربر، جهت جستجو در یک متن تعیین می‌شود. به بیان ساده‌تر اگر شما دنبال مکان حرف یا کلمه خاصی در یک رشته متنی می‌گردید، می‌بایست آن را در آرگومان Pattern قرار دهید.
- لازم است در آرگومان اول حداقل یک % وجود داشته باشد.
- حداکثر تعداد کاراکترهایی را که می‌توان در آرگومان اول قرار داد 8000 میباشد.
2- آرگومان دوم یا همان Expression : متنی که عملیات جستجو روی آن اعمال می‌گردد، در این آرگومان قرار می‌گیرد.
- اگر تعداد کارکترهای آرگومان دوم (Varchar(Max یا (nVarchar(Max باشد، در آن صورت Type خروجی تابع PATINDEX از نوع bigint میباشد، در غیر این صورت Type خروجی تابع PATINDEX از نوع Int است.
- اگر مقدار آرگومان دوم Null باشد، تابع PATINDEX مقدار Null بر می‌گرداند.
یک مثال کاربردی از تابع PATINDEX
یافتن اولین کاراکتر غیر عددی در رکودهای یک جدول
ابتدا در دیتابیس tempdb یک جدول به نام UsingPATINDEX مطابق Script زیر ایجاد،و چندین رکورد درون آن درج می‌نماییم:
USE tempdb GO CREATE TABLE UsingPATINDEX (ID INT, Words VARCHAR (100)) GO INSERT INTO UsingPATINDEX (ID, Words) SELECT 1, '1one' UNION ALL SELECT 2, '11eleven' UNION ALL SELECT 3, '2two' UNION ALL SELECT 4, '22twentytwo' UNION ALL SELECT 5, '111oneeleven' GO
در ادامه QUERY زیر را اجرا نمایید:
SELECT PATINDEX('%[a-z]%',Words) 'مکان اولین کاراکتر غیر عددی',
SUBSTRING(Words,PATINDEX('%[a-z]%',Words),1) 'نام اولین کاراکتر غیر عددی بعد از عدد',
Words 'متن اصلی'
FROM UsingPATINDEX
خروجی آن به شکل زیر است:
توضیح درباره QUERY :
قطعه کد زیر دنبال تمامی حروف a تا z ، درون فیلد Words می‌گردد و به اولین کاراکتر غیر عددی که می‌رسد، مکان آن را بر می‌گرداند.
PATINDEX('%[a-z]%',Words) 'مکان اولین کاراکتر غیر عددی'
قطعه کد زیر با توجه به مکان کاراکتر، خود کاراکتر را بر می‌گرداند:
SUBSTRING(Words,PATINDEX('%[a-z]%',Words),1) 'نام اولین کاراکتر غیر عددی بعد از عدد'
مثالی دیگر: فرض کنید،دنبال کلمه ای همانندensure می گردید، بطوریکه دو حرف اول و دو حرف آخر آن را بخاطر می‌آورید و حرف میانی آن را بخاطر نمی‌آورید، در آن صورت نیز می‌توانید از تابع PATINDEX استفاده نمایید، بدین شکل که به جای حرفی که بخاطر نمی‌آورید از _ استفاده کنید، همانند QUERY زیر:
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');
خروجی عدد 8 میباشد، که مکان حرف e است.
- در تابع PATINDEX می‌توانید براساس Collation دلخواه عملیات جستجو را انجام دهید، برای روش‌تر شدن مطلب شکل زیر را مشاهده نمایید:
همانطور که درابتدای مطلبم گفتم در آرگومان اول می‌توان از یک % استفاده نمود، به مثال زیر توجه نمایید:
Select PATINDEX('a%', 'abc')
خروجی آن مقدار یک است.
مثالی دیگر:
Select PATINDEX('%a', 'cba')
خروجی آن مقدار 3 میباشد.
- باید متذکر شوم، با دیدن دو مثال آخر،این تصور ایجاد نشود که تابع PATINDEX شبیه به تابع LIKE میباشد، چرا که تابع PATINDEX موقعیت کاراکتر را بر می‌گرداند، نه خود کاراکتر را،عملکرد تابع PATINDEX شبیه به عملکرد تابع CHARINDEX میباشد.
یکی از مواردی که در محیط کاری زیاد پیش می‌آید بحث همگام نبودن دیتابیس توسعه با دیتابیس کاری است.
منظور از دیتابیس توسعه، همان دیتابیسی است که برای برنامه نویسی و آزمایش از آن استفاده می‌شود و دیتابیس کاری هم مشخص است (برای مثال بر روی یک سرور در اینترانت داخلی یک شرکت و یا بر روی یک سرور اینترنتی قرار دارد). عادت‌های مختلفی هم اینجا ممکن است وجود داشته باشد، برای مثال تغییرات جدید بر روی دیتابیس کاری اعمال شود و سپس فراموش شود که همان‌ها نیز باید به دیتابیس توسعه هم اعمال شوند تا در تغییرات بعدی برای آزمایش دچار مشکل نشویم و برعکس. بعد از یک مدت هم تبدیل به کابوس می‌شود؛ نمی‌دانیم الان دیتابیس کاری جدیدتر است یا دیتابیس توسعه؛ و یا اینکه کلا دو دیتابیس مفروض چه تفاوت‌های ساختاری با هم دارند (بدیهی است بحث دیتا در اینجا در درجه‌ی اول اهمیت قرار ندارد). فرصت این هم وجود ندارد که تک تک جداول، ویووها، رویه‌های ذخیره شده و خلاصه تمامی اشیاء مرتبط را بررسی کنیم که چه اختلافی با هم دارند. اینجا مستندات هم کمکی نخواهند کرد چون صحبت از یک جدول با 5 فیلد در میان نیست که موارد را سریع و به صورت دستی تطابق دهیم. همچنین این مشکل عموما زمانی رخ می‌دهد که یکی از دو طرف در حال حاضر مستندات کامل و به روزی ندارد. اکنون چه باید کرد؟
اولین فکری که به ذهن خطور می‌کند مراجعه به ابزارهای جانبی است (مثلا Red Gate's SQL Compare چند صد دلاری) غافل از اینکه خود Visual studio 2008 (نگارش‌های تیمی و دیتابیسی) این قابلیت را نیز ارائه می‌دهد (شکل زیر).
پس از انتخاب new schema comparison ، در صفحه‌ای که ظاهر می‌شود، بر روی new connection کلیک کرده و دیتابیس‌های مبداء و مقصد را جهت مقایسه ساختاری انتخاب نمائید و سپس بر روی دکمه Ok کلیک کنید.
اگر اس کیوال سرور 2008 را نصب کرده باشید، با پیغام زیر روبرو خواهید شد:
برای رفع این مشکل باید بسته به روز رسانی زیر را نصب کرد تا این نگارش نیز پشتیبانی شود:
(برای نصب حتما باید SP1 مربوط به VS.Net 2008 پیشتر نصب شده باشد)
پس از کلیک بر روی دکمه Ok، کار آنالیز دو دیتابیس شروع شده و تفاوت‌ها گزارش داده می‌شوند:
همچنین جهت سهولت کار، اسکریپت T-SQL ایی را نیز به نام schema update script تولید می‌کند که با اجرای آن به سادگی کار به روز رسانی دیتابیس مقصد صورت خواهد گرفت.
در پایان یا می‌توان اسکریپت تولید شده را ذخیره کرد و در زمانی دلخواه اجرا و اعمال نمود و یا می‌توان بلافاصله بر روی دکمه write updates که در نوار ابزار ظاهر شده است کلیک کرد تا دیتابیس مقصد از لحاظ ساختاری با دیتابیس مبداء یکی شود.
