دسترسی به موقعیت ترتیبی (Ordinal Position) در تابع STRING_SPLIT در SQL Server و Azure SQL Database
تابع STRING_SPLIT در SQL Server 2016 و Azure SQL Database معرفی شد تا روشی بومی برای تقسیم یک رشته به جدولی از زیررشته‌ها ارائه دهد. پیش از این، توسعه‌دهندگان اغلب از توابع سفارشی تعریف‌شده توسط کاربر (UDFs) برای دستیابی به همین نتیجه استفاده می‌کردند. با این حال، آن UDFها در مقایسه با یک تابع بومی، اغلب عملکرد ضعیفی داشتند. تابع STRING_SPLIT چالش‌های عملکردی UDFها را حل می‌کند، اما یک محدودیت عمده دارد: موقعیت ترتیبی (ordinal position) را برای هر عنصر تقسیم شده نشان نمی‌دهد.
چرا موقعیت ترتیبی مهم است؟ اغلب، هنگام تقسیم یک رشته، موقعیت هر عنصر در رشته اصلی اهمیت دارد. برای مثال، اگر لیستی از آیتم‌ها دارید که ترتیب آن‌ها نشان‌دهنده اولویت یا دنباله‌ای از مراحل است، از دست دادن این ترتیب می‌تواند داده‌های تقسیم‌شده را کم‌فایده یا حتی بی‌معنی کند.
قبل از STRING_SPLIT، تکنیک‌های مختلفی برای تقسیم رشته‌ها و حفظ موقعیت ترتیبی استفاده می‌شد، مانند روشهای XML، CTEهای بازگشتی یا ترکیبات پیچیده‌ای از SUBSTRING و CHARINDEX. در حالی که این روش‌ها کار می‌کردند، اغلب طولانی، دشوار برای خواندن و گاهی اوقات مشکلات عملکردی خاص خود را داشتند.
یک راه‌حل رایج برای دستیابی به موقعیت ترتیبی با STRING_SPLIT در SQL Server 2016 به بالا و Azure SQL Database، استفاده از قابلیت‌های JSON است. با تبدیل رشته جدا شده با کاما به یک آرایه JSON، می‌توانید از OPENJSON استفاده کنید که به طور ذاتی یک ستون ‘key’ را که نشان‌دهنده موقعیت ترتیبی (با شروع از صفر) است، ارائه می‌دهد.
مثال زیر نحوه تبدیل یک رشته معمولی به فرمت JSON و سپس استفاده از OPENJSON برای استخراج آیتم‌ها به همراه موقعیت ترتیبی آن‌ها را نشان می‌دهد:
DECLARE @list NVARCHAR(MAX) = 'Apple,Banana,Orange,Grape';
SELECT
[key] AS OrdinalPosition,
[value] AS Item
FROM OPENJSON(N'["' + REPLACE(@list, ',', '","') + N'"]');
این روش ابتدا رشته جدا شده را به یک رشته آرایه JSON تبدیل می‌کند. برای مثال، رشته `Apple,Banana,Orange` به `[“Apple”,”Banana”,”Orange”]` تبدیل می‌شود. سپس، OPENJSON می‌تواند این رشته JSON را تجزیه کند و ستون `key` آن به طور خودکار موقعیت ترتیبی (بر پایه صفر) را به شما می‌دهد.
تبدیل رشته به فرمت JSON با استفاده از دستور زیر انجام می‌شود:
(N'[“‘ + REPLACE(@list, ‘,’, ‘”,”‘) + N'”]’);
این عبارت، رشته اصلی را به قالبی تبدیل می‌کند که OPENJSON بتواند آن را به عنوان یک آرایه JSON تشخیص دهد. این یکی از رویکردهای کارآمد برای به دست آوردن موقعیت ترتیبی با داده‌های رشته‌ای در SQL Server است.
اگرچه رویکرد JSON موثر است، اما سربار دستکاری رشته و تجزیه JSON را به همراه دارد. برای رشته‌های بسیار بزرگ یا عملیات مکرر، این سربار ممکن است قابل توجه باشد. با این حال، برای اکثر سناریوهای رایج، تعادل خوبی بین عملکرد و کارایی در مقایسه با UDFهای قدیمی‌تر یا روشهای XML فراهم می‌کند.
با انتشار SQL Server 2022، مایکروسافت این محدودیت را به طور مستقیم برطرف کرده است. تابع STRING_SPLIT اکنون یک پارامتر اختیاری به نام `ordinal` را شامل می‌شود. هنگامی که این پارامتر روی 1 تنظیم شود، تابع را راهنمایی می‌کند تا یک ستون اضافی به نام `ordinal` را برگرداند که حاوی موقعیت ترتیبی (بر پایه یک) هر عنصر است.
در ادامه یک مثال از نحوه استفاده از پارامتر `ordinal` در SQL Server 2022 آورده شده است:
-- This syntax requires SQL Server 2022 or Azure SQL Database with Compatibility Level 160 or higher
DECLARE @list NVARCHAR(MAX) = 'Apple,Banana,Orange,Grape';
SELECT
value AS Item,
ordinal AS OrdinalPosition
FROM STRING_SPLIT(@list, ',', 1);
قسمت اصلی این دستور که موقعیت ترتیبی را فراهم می‌کند، به شکل زیر است:
(STRING_SPLIT(@list, ‘,’, 1));
عدد 1 به عنوان پارامتر سوم، به تابع STRING_SPLIT می‌گوید که علاوه بر مقادیر تقسیم شده، ستون `ordinal` را نیز که حاوی ترتیب هر آیتم است، بازگرداند.
این ویژگی جدید در SQL Server 2022 فرآیند را به طور قابل توجهی ساده می‌کند و با توجه به اینکه یک پیاده‌سازی بومی است، عملکرد بهینه‌ای ارائه می‌دهد. این ویژگی نیاز به راه‌حل‌های پیچیده را از بین می‌برد و تابع STRING_SPLIT را برای کارهای دستکاری داده قدرتمندتر می‌کند.
برای کاربرانی که از Azure SQL Database استفاده می‌کنند، اطمینان حاصل کنید که سطح سازگاری پایگاه داده شما (Compatibility Level) روی 160 یا بالاتر تنظیم شده باشد تا بتوانید از پارامتر جدید `ordinal` در STRING_SPLIT استفاده کنید.
