en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
מאי5

Written by: ronen ariely
05/05/2012 22:31 RssIcon

הקדמה

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

* נציין כבר בהקדמה שפונקצית ISNUMERIC לא בודקת האם ערך של שרשרת מסוימת הוא תואם למספר! אלא האם הערך תואם לערך נומרי! והאם ניתן לכן להמיר אותו לערך מספרי של האלמנט הנומרי.

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


מה במדריך זה?

  1. הגדרה רשמית של הפונקציה ISNUMERIC בשפות ובשרתי SQL.
  2. הסבר המושג נומרי והקשר לשרתי SQL (שאילתות דוגמה).
  3. ההגדרה הפשוטה של ISNUMERIC בשרתי SQL (כסיכום להסברים הקודמים).
  4. הפתרון: פונקציות שונות לבדיקה האם שרשרת מסוימת מתאימה להיות מספר.
  5. השוואות ובדיקות מיטוב
  6. קישורים נוספים.

ההגדרה הרשמית של מייקרוסופט:

בשפות כמו VB ניתן למצוא פונקצית ISNUMERIC אבל הפונקציה שונה מזו שהוכנסה ב SQL. בעוד ב SQL הוכנסה פונקציה לבדיקת חוקיות נומרית, הרי שב VB אכן הפונקציה בודקת האם הערך הנו תואם למספר.שימו לב להבדל בין ההגדרה של הפונקציה ISNUMERIC בשפת VB לבין זו שהוכנסה ל SQL.

IsNumeric Function (Visual Basic)

Returns a Boolean value indicating whether an expression can be evaluated as a number.
http://msdn.microsoft.com/en-us/library/6cd3f6w1%28v=vs.90%29.aspx

ISNUMERIC (Transact-SQL)

Determines whether an expression is a valid numeric type.
http://msdn.microsoft.com/en-us/library/ms186272.aspx

* הבדל זה נוכל לנצל בקלות בהמשך כדי לעשות שימוש בפונקציה של VB או C# על ידי שימוש ב CLR תחת SQL.

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

* בשפות כמו VB אין לנו אובייקט נומרי מסוג Currency מובנה. אנו עושים שימוש בסוג Decimal במקום זה. ז"א מראש אנו עושים שימוש בערך המספרי ולא באלמנט הכולל נתון של סוג המטבע למשל. לכן פונקצית ISNUMERIC מתאימה לנו. בעוד ב SQL יש לנו סוגים מיוחדים שהם אובייקטים הכוללים מידע נוסף מאחורי הקלעים כמו במקרה של Currency בו יש לנו נתונים של ערך מספרי וכן סוג המטבע. זהו ההבדל המרכזי בין ערך מספרי ובין התאמה לסוג נומרי.


מה זה אלמנט נומרי, ומה תואם להיות נומרי?

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

Int, numeric, bigint, money, smallint, smallmoney, tinyint, float, decimal, real.

* שימו לב שהטקסט $10 אינו נומרי, אבל האובייקט מחיר $10 הוא נומרי ומתאים לסוג money.

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

ננסה לבצע המרה מפורשת של שרשרת לערך נומרי מסוג FLOAT:

SELECT CONVERT(FLOAT, '23d2') -- 2300
-- שרשרת זו נעזרת בסימון מוסכם להצגת 2 אפסים
-- לערך המספרי 23 נוספו שני אפסים אחריו

השרשרת עצמה אינה תואמת לפורמט מספרי. השרת מצליח לבצע את ההמרה כי מאחורי הקלעים הוא מזהה את הפורמט שלה כמתאים לאלמנט נומרי. ז"א אנחנו רואים שהשרת ביצע המרה של השרשרת שלנו לערך נומרי מסוג FLOAT ללא שגיאה. אכן אם נריץ את הפונקציה ISNUMERIC על שרשרת זו אז נקבל תוצאה חיובית, מפני שערך זה ניתן להמרה לפחות לאחד מהסוגים הנומריים שלנו.

SELECT ISNUMERIC('23d2') -- 1

כאמור בפיתוח בשפות כמו VB או C# אם היינו מנסים לבצע אותה פעולה למשל אז היינו מקבלים שגיאה:

Input string was not in a correct format

ההמרה המפורשת הבאה ב C# אינה מצליחה:

Console.WriteLine(
    Double.Parse("23d2")
);

נחזור לדיון ב SQL וננסה לבדוק המרה של ערך בפורמט המתאים לרישום "מחיר". הרצת שאילתה זו תעלה לנו שגיאה:

SELECT CONVERT(FLOAT, '$23')
-- Error converting data type varchar to float.

הסיבה ברורה. לסימון דולר אין משמעות בסוג נומרי FLOAT. אבל למרות זה נקבל תוצאה חיובית אם נריץ את השאילתה הבאה:

SELECT ISNUMERIC('$23') -- 1

תוצאה זו נובעת מכך שיש לנו סוג נומרי אחר money אשר כן מאפשר רישום מצורה זו.

SELECT CONVERT(money, '$23') – 23.00
SELECT CONVERT(money, '₪10') -- 10.00


ההגדרה הפשוטה של ISNUMERIC בשרתי SQL:

עתה אנחנו יכולים להבין שהפונקציה ISNUMERIC בודקת את החוקיות להמרה ל"לפחות אחד מהסוגים הנומריים" המותרים. אם הנתון חוקי להמרה כלשהיא אז הערך שיחזור לנו הוא 1, ואם ההמרה אינה חוקית אז נקבל את הערך 0.

 


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

על מנת לאפשר למי שצריך בדחיפות (המדריך הגיע כתוצאה משאלה ודיון בפורום) לעשות שימוש אז הנה דוגמה לפונקציית CLR בה ניתן לעשות שימוש יעיל לבדיקת ISNUMERIC (בהנמשך אני אשלב אני זה בקוד מלא ואצרף DLL מקושר למי שרוצה להתקין ישר).


פתרון פשוט בעזרת CLR

// פתרון זה לא מכסה עדיין את כל המצבים ופשוט מתבסס על יכולת המתודה
// Parse
// לבצע את ההמרה ובדיקה שההמרה בוצעה טוב
// המשך המדריך ופונקציות יעילות ו/או מורכבות יותר אשים כשאגיע לכתיבת המשך המדריך
// בינתיים ניתן לעבוד עם פונקציה זו או עם הפתרון הישיר של
// TSQL
// שמכסה אני חושב את כל המצבים והוא כרגע הפתרון הסופי שלי בצורה ישירה
  
public static Boolean IsNumeric01(Object Obj)
  
{
  
    if (Obj == null || Obj is DateTime)
  
        return false;
  
   
    if (Obj is Int16 || Obj is Int32 || Obj is Int64 || Obj is Decimal || Obj is Single || Obj is Double || Obj is Boolean)
  
        return true;
  
   
    try
  
    {
  
        if (Obj is string)
  
            Double.Parse(Obj as string);
  
        else
  
            Double.Parse(Obj.ToString());
  
        return true;
  
    }
  
    catch { } // just dismiss errors but return false
  
    return false;
  
}
  
// if you use VB.Net you can us the build in function: IsNumeric
// and you dont need any code

פתרון ברמת TSQL נקי

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

select isnumeric(Field_Name + 'e0')
from tabl_name

במקרה בו רוצים פתרון כללי יותר שמכסה את כל המצבים כולל מצב בו רוצים לאשר מספרים הכוללים הפרדה של פסיקים שיש במספר כמו למשל 1,000 או 12,345,678.9: אז נוכל להעזר בפונקציה הבאה. במקרה זה הפתרון בעזרת CLR הרבה יותר יעיל ומומלץ.

CREATE function Ariely_Isnumber (@STR nvarchar(100))
Returns Bit
As
Begin
    /***************************************************************/
    declare @Ret as bit;
    declare @Point int = CHARINDEX('.', reverse(@STR));
    declare @INT Varchar(max) = left(@STR,len(@STR) - @Point);
 
    -- נבדוק ללא פסיקים
    -- מכסה את כל המקרים בלי פסיקים ולכן אם לא היה צורך גם בפורמט עם פסיקים זה היה מהיר
    set @Ret = isnumeric(replace(@STR,',','') + 'e0')
    -- בדיקת מיקום פסיק ראשוןן
    IF @Point + 4  > CHARINDEX(',', reverse(@STR)) and CHARINDEX(',', reverse(@STR)) > 0
    BEGIN
        set @Ret = 0
    END
    -- בדיקת מיקומי הפסיקים הבאים כך שהם מגיעים כל 3 תווים
    IF
        PatIndex('%,[^,][^,][^,][^,]%',@INT) > 0 or
        PatIndex('%,[^,][^,],%',@INT)> 0 or
        PatIndex('%,[^,],%',@INT)> 0 or 
        PatIndex('%,,%',@INT)> 0
    begin
        set @Ret = 0
    END
    /***************************************************************/
    return @Ret
End

Tags: SQL , isnumeric , C# , clr
Categories: SQL