היכן הפסיק ה-n-י? (מציאת מופע של תת מחרוזת)

12/03/2010

אין תגובות

(פורסם לראשונה ב-www.SqlServer.co.il)

נתונה לנו מחרוזת עם מספר ארצות-'Israel,England,Argentina,India,Kenya,Australia'. מה המדינה השלישית ברשימה?

למצוא את הראשונה על ידי T-SQL קל- בעזרת פונקצית CharIndex נמצא את הפסיק הראשון, וכל מה שלפניו זו המדינה הראשונה:

Declare    @S VarChar(Max)= 'Israel,England,Argentina,India,Kenya,Australia',

@I Int;

Set        @I=CHARINDEX(',',@S);

Select    SubString(@S,1,@I-1) FirstCountry;

אבל כדי למצוא את המדינה השלישית יש להתחיל לחפש את השני בעזרת CharIndex לאחר הראשון, ואת השלישי לאחר השני, והמדינה היא תת המחרוזת שבין השני לשלישי.. מסובך:

Declare    @S VarChar(Max)= 'Israel,England,Argentina,India,Kenya,Australia',

        @I1 Int,

        @I2 Int,

        @I3 Int;

Set        @I1=CHARINDEX(',',@S);

Set        @I2=CHARINDEX(',',@S,@I1+1);

Set        @I3=CHARINDEX(',',@S,@I2+1);

Select    SubString(@S,@I2+1,@I3-@I2-1) ThirdCountry;

את הבעייה ניתן לפתור באופן רקורסיבי ולשם כך נבנה טבלה לדוגמה עם מספר סטודנטים ומקצועות הלימוד שלהם:

Use tempdb;

Go

Create Table T_Students (Name VarChar(50), Subjects VarChar(Max));

Go

Insert Into T_Students

Values    ('Ana','Geography,Literature'),

        ('Beni','Chemistry,Economics,History,Sociology'),

        ('Carmel',''),

        ('Dana','Literature,Economics,Philosophy,Sociology'),

        ('Eli','Zoology,Economics,Geography,History,Science'),

        ('Felicia','Medicine');

Go

וכעת נשלוף את הנתונים בעזרת CTE:

With MyCTE As

------------------------------------------------------------------------------------------------------------------------------------------------

(Select Num, 

        Name,

        Subjects,

        [Begin],

        Comma,

        SUBSTRING(Subjects,[Begin],Comma-1) Subject

From    (Select 1 Num,

                Name,

                Subjects+',' Subjects,

                1 [Begin],

                CHARINDEX(',',Subjects+',') Comma

        From    T_Students

        Where   Subjects<>'') T

------------------------------------------------------------------------------------------------------------------------------------------------ 

Union All

Select    Num,

        Name,

        Subjects,

        [Begin],

        Comma,

        SUBSTRING(Subjects,[Begin],Comma-[Begin]) Subject

From    (Select Num+1 Num,

                Name,

                Subjects,

                Cast(Comma As Int)+1 [Begin],

                CHARINDEX(',',Subjects,Comma+1) Comma

        From    MyCTE

        Where   CHARINDEX(',',Subjects,Comma+1)<>0) T)

------------------------------------------------------------------------------------------------------------------------------------------------

Select    * 

From    MyCTE

Order By Name,

        Num;

החלק הראשון שבין הקו הראשון לשני הוא ה"עוגן" (Anchor), והוא מוצא בעזרת השאילתה המקוננת את מקומו של הפסיק הראשון ואת המקצוע הראשון של כל סטודנט.

החלק השני שבין הקו השני לשלישי הוא החלק הרקורסיבי, והוא מוצא את הפסיק "הבא" ואת המקצוע "הבא", ולשליפה צורף מונה רץ Num שמאפשר לדעת אם מדובר בפסיק הראשון או השני או השלישי וכו'.

השאילתה המקוננת מחשבת איפוא-

* את המונה הרץ Num,

* שולפת את שם הסטודנט Name,

* את שמות המקצועות (בעוגן נוסיף פסיק בסוף מטעמי נוחות כדי שמספר הפסיקים יהיה כמספר המקצועות) Subjects,

* את נקודת ההתחלה Begin– שבעוגן היא 1 ובחלק הרקורסיבי היא 1 אחרי הפסיק הקודם שנמצא,

* ואת הפסיק הבא לאחר הקודם.

השאילתה החיצונית (בעוגן ובחלק הרקורסיבי) מחשבת את המקצוע שמופיע בין הפסיק הקודם (Begin) והנוכחי (Comma).

כעת נוכל על פי הצורך לשנות את החלק האדום שמופיע בסוף ושולף מה-CTE הרקורסיבי.

אם נרצה למצוא את מקומו של הפסיק הרביעי (ואיתו את המקצוע הרביעי) נשלוף כך-

Select  Name,

        Comma,

        Subject

From    MyCTE

Where    Num=4

        And Comma<>Len(Subjects)

Order By Name;

התנאי השני Comma<>Len הוא אופציונלי ונועד למקרה שלא נרצה לכלול בחשבון את הפסיק הפיקטיבי שהוספנו מטעמי נוחות בסוף כל מחרוזת (החלק המקונן בעוגן).

ואם נרצה נוכל לקבל את הטבלה המקורית מפורקת (Split) ומנורמלת:

Select  Name,

        Subject

From    MyCTE

Order By Name;

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *