המרה בין ערכים דצימליים והקסדצימלים ומיון ערכים מספריים

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

להלן דוגמה מדורגת כיצד מתרגמים 500 לערך הקס דצימלי:

With T As

(Select 500 [Decimal],

        Cast(500 As Varbinary(Max)) [Hexadecimal As Varbinary],

        master.dbo.FN_VarbinToHexStr(Cast(500 As Varbinary(Max))) [Hexadecimal As Varchar],

        Stuff(master.dbo.FN_VarbinToHexStr(Cast(500 As Varbinary(Max))),1,2,'')

        [Hexadecimal without 0x])

Select  *,

        Stuff([Hexadecimal without 0x],1,PatIndex('%[^0]%',[Hexadecimal without 0x])-1,'') [Hexadecimal without leading '0']

From T;

העמודה הראשונה משמאל מציגה את הערך 500,

השניה את התרגום שלו ל-Varbinary המהווה הצגה הקסדצימלית,

השלישית מעבר ל-Varchar רגיל שניתן לנקות ולסדר בעזרת FN_VarbinToHexStr (פונקציית מערכת לא מתועדת),

הרביעית ללא ה-"0x" שבהתחלה,

והחמישית ללא האפסים המובילים (PatIndex מוצאת את התו הראשון שאינו 0, ואת כל האפסים שלפניו מנקים בעזרת Stuff).

לבדיקה- התוצאה 1f4 שווה:

1*16²+15*16¹+4*16⁰=500

השליפה הנ"ל מעט מורכבת כי ניקוי האפסים המובילים מצריך פניה כפולה לערך שלפניו והיה מיותר "לשתול" פעמיים את הביטוי המורכב של העמודה הרביעית בתוך החישוב של ה-PatIndex & Stuff.

לתרגול- נשלוף את מספרי האובייקטים מתוך sys.objects ונתרגם אותם להקסדצימלית:

With T As

(Select object_id,

        Stuff(master.dbo.FN_VarbinToHexStr(Cast(object_id As Varbinary(Max))),1,2,'') oi_hex

From sys.objects),

T1 As

(Select object_id,

        Stuff(oi_hex,1,PatIndex('%[^0]%',oi_hex)-1,'') Hx

From T)

Select *

From T1;

מה בכיוון ההפוך?

קצת יותר מורכב מכיוון שלא מצאתי פונקציה קיימת שעושה זאת, ולכן אין ברירה אלא לכתוב פונקציה כזו שעוברת בלולאה על כל התווים וסוכמת את מכפלתם בחזקה המתאימה של 16 (כפי שעשיתי בבדיקה של 1f4), או להשתמש בטבלת מספרים שמאפשרת גישה לכל אחד מהתווים וביצוע Group By & Sum.

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

With N As

(Select 1 I

Union All

Select  I+1

From    N

Where   I<100)

Select  Hx,

        Sum(Case Substring(Reverse(Hx),I,1) When 'a' Then 10

                                            When 'b' Then 11

                                            When 'c' Then 12

                                            When 'd' Then 13

                                            When 'e' Then 14

                                            When 'f' Then 15

                                            Else Cast(Substring(Reverse(Hx),I,1) As Int)

                                            End*Power(16,I-1)) [Int]

From    #T

Inner Join N

        On N.I<=Len(#T.Hx)

Group By Hx

Order By Int;

לסיום- כיצד ממיינים את הערכים?

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

Select  *

From    #T

Order By Hx;

אפשר לראות שהערך 19 מופיע אחרי 18d6a699 שגדול ממנו בהרבה בדיוק כשם שהמלה "שי" מופיעה במילון אחרי "אקליפטוס", וזו בעייה שנתקלים בה לא פעם עם ערכים מספריים הנשמרים בעמודות טקסט (למשל- תעודות זהות).

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

Select  *

From    #T

Order By Len(Hx),

        Hx;