הבלוג של גרי רשף

27/12/2010

פונקציית המחרוזת Stuff

Filed under: Uncategorized — תגיות: , , , , , , — גרי רשף @ 21:40

בעוונותי הרבים לא הכרתי את הפונקציה הזו עד לאחרונה, ומאז אני עושה בה שימוש רב.

הפונקציה משלימה את Replace, כאשר במקום להחליף תת מחרוזת ספציפית אחת באחרת – לא משנה מה מיקומה, היא מחליפה תת מחרוזת לא ספציפית אחת באחרת – אך במקום מסויים.

למשל- בכתיבת SQL דינאמי אני נדרש לעיתים לשרשר את שמות כל העמודות של טבלה כלשהי כדי ליצור פקודת Insert Into, כמובן- עם פסיקים בין שמות העמודות. למשל:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+','+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Print @SQL;

 

המחרוזת המתקבלת היא ,fileid,filename,name,status והיא כוללת פסיק מיותר בתחילתה.

לא ניתן להיפטר ממנו על ידי Replace כי אז כל הפסיקים יוחלפו, ופתרון בעזרת Substring (או Right / Left) השולף מתוך SQL@ את תת המחרוזת מהמקום השני ואילך הוא מעט מסורבל:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+','+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Set @SQL=Right(@SQL,Len(@SQL)-1);

Print @SQL;

 

פתרון זה מעט מסורבל מכיוון שלעיתים יש להפעיל את את זה על ביטוי מורכב יותר, ולחזור עליו פעמיים זה מאוד לא נוח.

במקרה זה ניתן להשתמש ב-Stuff שמחליפה את התו הראשון (מיקום 1, אורך 1) במחרוזת ריקה:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+','+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Set @SQL=Stuff(@SQL,1,1,'');

Print @SQL;

 

מה קורה אם הפסיק המיותר נמצא בסוף המחרוזת?

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+name+',' From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Print @SQL;

 

המחרוזת המתקבלת היא fileid,filename,name,status,.

גם כאן ניתן להשתמש ב-Left (במקום Right בדוגמה למעלה) שעלול להיות מסובך אם במקום SQL@יהיה לנו ביטוי מורכב,

או ב-Stuff כך:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+name+',' From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Set @SQL=Reverse(Stuff(Reverse(@SQL),1,1,''));

Print @SQL;

 

זה טיפה מסובך (לבצע Reverse כדי שהפסיק האחרון יהיה ראשון, להסיר אותו, ולבצע Reverse נוסף) אך SQL@ מצוטט פעם אחת.

לסיום- מבלי לבטל דבר מכל מה שכתבתי, במקרה מסויים זה ניתן עם קצת תושיה למנוע מראש את היווצרות הפסיק המיותר (בתנאי ש-SQL@ הוא Null):

Declare @SQL Varchar(Max);

Select @SQL=IsNull(@SQL+',','')+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Print @SQL;

 

דוגמה שימושית: יש טריק פשוט שמאפשר לבצע פעולה הדומה ל-Group By על עמודה טקסטואלית כך שתשרשר את הערכים המופיעים. למשל:

Select    type,

        Stuff((Select ','+name AS [text()]

                From Sysobjects T1

                where T1.type=T.type

                order by name

                For XML Path('')),1,1,'') As name

From    Sysobjects T

Group By T.type;

image

השליפה מבצעת group By על type כך שכל ערך יופיע פעם אחת,

 

ולכל type מתבצעת שליפת XML משנית של ערכי name שלו (יחד עם פסיק מוביל) שלמעשה משרשרת אותם,

ועל שליפת ה-XML הזו מופעלת פונקציית Stuff כדי להסיר את הפסיק הראשון המיותר כמו בדוגמה הראשונה.

לו לא היינו משתמשים ב-Stuff אלא ב-Right היינו צריכים לצטט פעמיים את שליפת ה-XML המשנית וזה היה כמובן יותר מסורבל:

Select    type,

        Right((Select ','+name AS [text()]

                From Sysobjects T1

                where T1.type=T.type

                order by name

                For XML Path('')) ,

            Len((Select ','+name AS [text()]

                From Sysobjects T1

                where T1.type=T.type

                order by name

                For XML Path('')))-1) As name

From    Sysobjects T

Group By T.type;

יש מקרים מורכבים גם יותר מזה..

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: