تقسیم رشته SQL Server با OPENJSON راهکار کارآمد

تقسیم رشته در SQL Server با OPENJSON: راهکار کارآمد و قدرتمند

تقسیم رشته SQL Server با OPENJSON: در توسعه SQL Server، نیاز به تقسیم رشته‌ها به اجزای کوچک‌تر یک وظیفه متداول است. این عملیات می‌تواند چالش‌برانگیز باشد، زیرا اغلب به راهکارهای پیچیده و گاهی اوقات پرهزینه نیاز دارد. در طول سال‌ها، توسعه‌دهندگان از روش‌های مختلفی مانند توابع XML، CLR (Common Language Runtime) و CTEهای بازگشتی (Recursive Common Table Expressions) برای انجام این کار استفاده کرده‌اند. با این حال، با معرفی `STRING_SPLIT` در SQL Server 2016 و سپس `OPENJSON`، گزینه‌های قدرتمندتری در دسترس قرار گرفته‌اند که کارایی و انعطاف‌پذیری بیشتری را ارائه می‌دهند.

`STRING_SPLIT` یک تابع جدولی است که یک رشته و یک جداکننده را می‌پذیرد و نتایج را به صورت ردیف‌هایی از زیررشته‌ها برمی‌گرداند. این تابع بسیار ساده و کارآمد است، اما یک محدودیت مهم دارد: **نمی‌تواند ترتیب (ordinal position) اجزای تقسیم‌شده را برگرداند.** این بدان معناست که اگر ترتیب اجزا برای شما اهمیت دارد، `STRING_SPLIT` به تنهایی کافی نخواهد بود.

مثالی از استفاده از `STRING_SPLIT`:

SELECT value FROM STRING_SPLIT('اپل,موز,پرتقال', ',');

این کوئری مقادیر ‘اپل’، ‘موز’ و ‘پرتقال’ را به صورت جداگانه برمی‌گرداند، اما شما نمی‌دانید ‘اپل’ اولین آیتم بوده یا خیر.

برای غلبه بر این محدودیت و بهره‌گیری از قابلیت‌های پیشرفته‌تر، می‌توانیم از `OPENJSON` استفاده کنیم. `OPENJSON` یک تابع جدولی است که داده‌های JSON را تجزیه می‌کند و آن‌ها را به صورت یک مجموعه ردیفی برمی‌گرداند. با استفاده از این تابع، می‌توانیم یک رشته معمولی را به فرمت JSON تبدیل کرده و سپس با `OPENJSON` آن را تجزیه کنیم تا نه تنها مقادیر، بلکه ترتیب آن‌ها را نیز به دست آوریم. این روش به ویژه در SQL Server 2016 و نسخه‌های بالاتر کارایی بالایی دارد، جایی که پشتیبانی از JSON به طور کامل ادغام شده است.

تصور کنید لیستی از مقادیر دارید که با کاما از هم جدا شده‌اند و می‌خواهید آن‌ها را تقسیم کنید. ابتدا، باید این رشته را به یک آرایه JSON تبدیل کنیم. این کار با جایگزینی جداکننده‌ها با ساختار JSON مناسب انجام می‌شود. برای مثال، یک رشته ‘اپل,موز,پرتقال’ می‌تواند به `[“اپل”,”موز”,”پرتقال”]` تبدیل شود.

در اینجا یک نمونه از نحوه استفاده از `OPENJSON` برای تقسیم رشته و به دست آوردن موقعیت‌های ترتیبی آورده شده است:

DECLARE @List varchar(100) = 'اپل,موز,پرتقال';

SELECT [key] AS OrdinalPosition, value AS Item
FROM OPENJSON('["' + REPLACE(@List, ',', '","') + '"]');

در این اسکریپت، ابتدا متغیر `@List` را تعریف می‌کنیم. سپس، با استفاده از تابع `REPLACE`، تمام کاماها را با ترکیب `”,”` جایگزین می‌کنیم. در نهایت، با افزودن `[“` در ابتدا و `”]` در انتها، رشته را به یک آرایه JSON معتبر تبدیل می‌کنیم. `OPENJSON` سپس این آرایه JSON را تجزیه کرده و دو ستون کلیدی را برمی‌گرداند: `key` که ایندکس مبتنی بر صفر آیتم در آرایه JSON است (که به عنوان `OrdinalPosition` استفاده می‌شود) و `value` که خود آیتم تقسیم شده است.

این تکنیک بسیار انعطاف‌پذیر است و به شما امکان می‌دهد با جداکننده‌های مختلف کار کنید. برای مثال، اگر جداکننده شما `~` باشد:

DECLARE @List varchar(100) = 'کتاب~قلم~کاغذ';

SELECT [key] AS OrdinalPosition, value AS Item
FROM OPENJSON('["' + REPLACE(@List, '~', '","') + '"]');

این کوئری به همان شیوه کار می‌کند و ‘کتاب’, ‘قلم’, ‘کاغذ’ را با موقعیت‌های ترتیبی مربوطه برمی‌گرداند. این روش برای زمانی که داده‌های شما دارای جداکننده‌های نامنظم یا غیرمعمول هستند، بسیار مفید است.

برای استفاده مجدد و ساده‌سازی این منطق، می‌توانید یک تابع تعریف شده توسط کاربر (UDF) بسازید. این UDF یک رشته و یک جداکننده را می‌پذیرد و یک جدول حاوی مقادیر تقسیم‌شده و موقعیت‌های ترتیبی آن‌ها را برمی‌گرداند. این کار باعث می‌شود کد شما تمیزتر و قابل نگهداری‌تر باشد و عملکرد را نیز بهبود می‌بخشد، زیرا تابع فقط یک بار کامپایل می‌شود.

در اینجا نحوه ایجاد یک تابع جدولی (Table-Valued Function) برای تقسیم رشته با `OPENJSON` آمده است:

CREATE FUNCTION dbo.udf_SplitStringWithOrdinal
(
    @String NVARCHAR(MAX),
    @Delimiter NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        [key] AS OrdinalPosition,
        value AS Item
    FROM OPENJSON('["' + REPLACE(@String, @Delimiter, '","') + '"]')
);

پس از ایجاد این تابع، می‌توانید به سادگی آن را در کوئری‌های خود فراخوانی کنید، درست مانند یک جدول معمولی:

SELECT OrdinalPosition, Item
FROM dbo.udf_SplitStringWithOrdinal('SQL Server,پایگاه داده,برنامه نویسی', ',');

این تابع به شما امکان می‌دهد تا هر رشته‌ای را با هر جداکننده‌ای به راحتی تقسیم کنید و موقعیت ترتیبی هر آیتم را نیز به دست آورید. استفاده از `OPENJSON` برای تقسیم رشته‌ها نه تنها یک راهکار مدرن و کارآمد است، بلکه با بهره‌گیری از قابلیت‌های بومی JSON در SQL Server، عملکرد خوبی را نیز ارائه می‌دهد. این روش به ویژه برای سناریوهایی که نیاز به حفظ ترتیب آیتم‌ها دارید و `STRING_SPLIT` محدودیت دارد، ایده‌آل است و آن را به ابزاری قدرتمند در جعبه ابزار هر توسعه‌دهنده SQL Server تبدیل می‌کند.