محتویات سایت
        برچسب های محبوب 








 
   تجزیه کردن یک رشته به عناصر آن
  Comma-Seprated List یک رشته ای است که عناصر/آیتم های آن با یک کاراکتری از هم تفکیک شده اند. برای تبدیل این رشته به عناصر آن نیاز به بحث Splitting/Parsing وجود دارد. که این مقاله به برخی از تکنیک ها و راه حل های Set-Based اشاره می کند.
   SQL Server
   ۲۰۹۲۷
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۸۹/۳/۱۰
نسخه قابل چاپ نسخه قابل چاپ

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(@SCHARINDEX('.'@S) - 1) 
       END AS 'Separated'
UNION ALL 
SELECT CASE WHEN LEN(@S) - LEN(REPLACE(@S'.''')) IN (2, 3) 
                 THEN SUBSTRING (@SCHARINDEX('.'@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 (@SCHARINDEX('.'@SCHARINDEX('.'@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(@SCHARINDEX('.'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(MAXNOT 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)
*/