split (تقسیم کردن، تجزیه کردن) عکس عمل concatenate (الحاق کردن) است.
همانطوری که در خیلی از سناریوها احتیاج داریم سطرهای جدول را با همدیگر الحاق کنیم، زمانی نیز فرار می رسد که نیاز به تجزیه کردن عناصر یک مقدار به چند سطر داریم. به تجزیه کردن یک رشته به اطلاح String Parsing یا Splitting گفته می شود.
اجازه بدین صورت مساله را با یک مثال شهودی شرح بدهم. به تصویر زیر توجه کنید، الحاق کردن سطرهای جدول و تبدیل آنها به یک لیست و نمایش دادن در یک فیلد و تفکیک آنها با یک کاراکتر (delimiter) به Concatenating معروف است. و بر عکس این عمل نیز به Splitting یا Parsing شهرت دارد.
اگر تعداد Item هایی که باید از هم تجزیه شوند بسیار محدود باشد (مثلا کمتر از 5)، می توانیم با روشهای بسیار ساده این کار را انجام دهیم.
String Parsing بوسیله ی PARSENAME
تابع PARSENAME برای جداسازی و تشخیص اجزای یک شی بکار می رود یعنی نام شی (Object Name)، نام مالک (Owner)، نام Database و نام Server. در صورتی که کلمات الحاق شده در رشته بیشتر از 4 نباشند می توانیم از این تابع کمک بگیریم.
DECLARE @S VARCHAR(500)='Professional.Specialist.Expert.Authority';
SELECT PARSENAME(@S, 4) AS Seprated
UNION ALL SELECT PARSENAME(@S, 3)
UNION ALL SELECT PARSENAME(@S, 2)
UNION ALL SELECT PARSENAME(@S, 1)
EXCEPT SELECT NULL;
/* Result
Seprated
----------------
Authority
Expert
Professional
Specialist
*/
Parsing String بوسیله ی توابع رشته ای
کد زیر شاید با نگاه اول کمی هولناک به نظر برسد، اما از منطق ساده ای پیروی می کند، تنها نکته ی که وجود دارد بحث توابع CHARINDEX تو در تو (nested) است. باید بگویم که این تنها یک مثال است و در عمل قابل استفاده نیست. تنها برای تکمیل مقاله به این روش اشاره کرده ام.
DECLARE @S VARCHAR(500)='Professional.Master.Specialist.Expert';
SELECT CASE WHEN CHARINDEX('.', @S) = 0 THEN @S
ELSE LEFT(@S, CHARINDEX('.', @S) - 1)
END AS 'Separated'
UNION ALL
SELECT CASE WHEN LEN(@S) - LEN(REPLACE(@S, '.', '')) IN (2, 3)
THEN SUBSTRING (@S, CHARINDEX('.', @S) + 1,
CHARINDEX('.', @S,
CHARINDEX('.', @S) + 1) -
CHARINDEX('.',@S) - 1)
ELSE NULL
END
UNION ALL
SELECT CASE WHEN LEN(@S) - LEN(REPLACE(@S, '.', '')) = 3
THEN SUBSTRING (@S, CHARINDEX('.', @S, CHARINDEX('.', @S) + 1) + 1,
CHARINDEX('.', @S,
CHARINDEX('.', @S,
CHARINDEX('.', @S) + 1) + 1) -
CHARINDEX('.', @S,
CHARINDEX('.', @S) + 1) - 1)
ELSE NULL
END
UNION ALL
SELECT CASE WHEN LEN(@S) - LEN(REPLACE(@S, '.', '')) IN (1, 2, 3)
THEN RIGHT(@S, CHARINDEX('.', REVERSE(@S)) - 1)
ELSE NULL
END
EXCEPT SELECT NULL;
/* Result
Separated
----------------
Expert
Master
Professional
Specialist
*/
دو روش قبلی نمی توانند نیازهای ما را به طور کلی بر طرف کنند، روش PARSENAME که محدود به چهار قسمت می باشد و روش بعد از آن، خوانایی بسیار ضعیفی داشته و توسعه ی آن بسیار دشوار است.
راه حلهای رایجی برای حل این مساله بطور کلی مطرح شده اند، از جمله استفاده از multi statement TVF و کمک گرفت از جدول اعداد. که در ادامه روش دوم یعنی کمک گرفتن از جدول اعداد را قرار می دهم.
همانطور که در ادامه مشاهده خواهید کرد با استفاده ی صحیح از جدول اعداد مسائل ظاهرا پیچیده به سادگی با روشهای مجموعه گرا حل خواهند شد. پس توسیعه می کنم قدر این جدول اعداد را بدانید و مثل یک جعبه ابزار همیشه در کنار دستتان داشته باشید.
به تصویری که اول مقاله وجود دارد مجددا توجه کنید، می خواهیم جدولی با نام Concatenated که دقیقا مشابه قسمت سمت راستی تصویر است ایجاد کنیم، سپس بویسله ی یک Query نتیجه ی مشابه جدول سمت چپ تصویر را تولید کنیم. برای اینکار نیاز به یک table type و یک متغیر جدولی با داده های مورد نظر و یک SP برای برگرداندن سطرهای مورد نظر داریم. که البته در Query که داخل SP می باشد نیاز به یک TVF است که کار Split را انجام می دهد. و در تابع Splitter هم نیاز به یک جدول اعداد داریم (زیرا تابع با کمک جدول اعداد رشته را تجزیه می کند).
برای تعریف متغیر جدولی ابتدا نیاز به یک User-Defined Table Type وجود دارد، سپس یک متغیر جدولی از نوع جدولی که ایجاد کردیم تعریف می کنیم و داده های مورد نظر را در آن انتشار می دهیم. ولی قبل از هرچیز نیاز داریم یک جدول اعداد ایجاد کنیم.
یک تابع ایجاد می کنیم که به تعداد مورد نظر ما اعداد متوالی تولید و بر گرداند سپس از این تابع در دستور SELECT INTO برای ایجاد جدول اعداد با تعداد سطر مورد نظر استفاده می کنیم. دستورات مربوط به ایجاد تابع و استفاده از آن برای ساخت جدول به قرار زیر است:
--TVF for publishing and returning sequence numbers
CREATE FUNCTION dbo.Numbers (@N INT) RETURNS TABLE AS
RETURN (WITH RecCTE (nbr) AS
(SELECT 1
UNION ALL
SELECT nbr + 1 FROM RecCTE WHERE nbr < 100),
Nums (nbr) AS
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM RecCTE AS C1
CROSS APPLY
RecCTE AS C2
CROSS APPLY
RecCTE AS C3)
SELECT nbr
FROM Nums
WHERE nbr <= @N);
GO
--Creating Number Table using TVF
SELECT n.nbr
INTO Nums
FROM dbo.Numbers(10000) AS n;
GO
بعد از این مرحله امکان ایجاد تابع Splitter بوجود آمده است. همانطوری که مشاهده می کنید استفاده ی بجا از جدول اعداد کار را بسیار ساده تر خواهد کرد و همچنین مسائل را روش های مجموعه گرا حل می کند.
--TVF for Splitting the String
CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS
RETURN (SELECT CASE WHEN CHARINDEX(@D, @S + @D, nbr) - nbr = 0 THEN ''
ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) - nbr)
END AS Word, nbr
FROM Nums
WHERE nbr <= LEN(@S)
AND SUBSTRING(@D + @S, nbr, 1) =@D);
GO
اکنون یک Table Type ایجاد می کنیم، برای سادگی کار یک ستون به نام "کد نوع" ایجاد کردیم و یک ستون محاسباتی با کمک عبارت CASE بر اساس آن ستون تعریف کرده ایم که کار ورود اطلاعات را ساده تر کند. و یک قید چک نیز روی ستون اصلی ایجاد کردیم تا تنها سه مورد خاص در آن درج شود. (البته این یک مثال ساده است ممکن است در سناریوهای واقعی اینگونه رفتار نکنیم)
CREATE TYPE Concatenated AS TABLE
(
[Type_ID] TINYINT NOT NULL,
[Type_Name] AS CASE [Type_ID]
WHEN 1 THEN 'Colors'
WHEN 2 THEN 'Occupations'
WHEN 3 THEN 'Numbers'
END,
[List] VARCHAR(MAX) NOT NULL,
CHECK ([Type_ID] BETWEEN 1 AND 3),
PRIMARY KEY ([Type_ID])
);
سپس یک SP با یک پارامتر از نوع جدولی (TVP) تعریف می کنیم که نتیجه ی مورد نظر ما را بدست آورد. اینجاست که قدرت عملگر غیر استاندارد جدولی APPLY نمایان می شود. برای حل مساله تنها راه موجود استفاده از CROSS APPLY می باشد.
CREATE PROC MySP (@MyTable Concatenated READONLY) AS
SELECT T.[Type_name],
D.word
FROM @MyTable AS T
CROSS APPLY dbo.splitter(T.List, '.') AS D
ORDER BY T.[Type_ID];
GO
سپس یک متغیر جدولی از نوعی که قبل ایجاد کردیم تعریف کرده و سطرهای مورد نظر را در آن درج می کنیم و در پایان SP را با پارامتر مذکور اجرا می کنیم:
DECLARE @MyTable AS Concatenated;
INSERT INTO @MyTable([Type_ID], [List])
VALUES (1, 'Red.Green.Yellow.White.Orange.Pink'),
(2, 'Teacher.Trainer.Consultant.Developer.Programmer'),
(3, 'One.Two.Three.Four.Fives');
SELECT [type_name], [list] FROM @MyTable;
/*--======== Before Executing the Procedure
type_name list
----------- ----------------------------------------------------
Colors Red.Green.Yellow.White.Orange.Pink
Occupations Teacher.Trainer.Consultant.Developer.Programmer
Numbers One.Two.Three.Four.Fives
*/
EXECUTE MySP @MyTable;
/*--======= After Executing the Procedure
Type_name word
----------- ----------
Colors Red
Colors Green
Colors Yellow
Colors White
Colors Orange
Colors Pink
Occupations Teacher
Occupations Trainer
Occupations Consultant
Occupations Developer
Occupations Programmer
Numbers One
Numbers Two
Numbers Three
Numbers Four
Numbers Fives
(16 row(s) affected)
*/