SQL Server Performance

Enhanced ISNUMERIC() function

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, May 28, 2007.

  1. Madhivanan Moderator

    As ISNUMERIC() is not always reliable(as it returns 1 for 12d1,$12,etc), the following can be used to identity if the string has proper numerals

    CREATE Function Is_numeric(@value varchar(25))
    Returns bit
    as
    Begin
    Return
    (
    case
    when @value not like '%[^-0-9.]%' and ((charindex('.',@value)>0 and len(@value)-len(replace(@value,'.',''))=1 and len(@value)>1) or charindex('.',@value)=0)
    and
    1=
    (
    case when charindex('-',@value)>0 then
    case when left(@value,1)='-' and len(@value)-len(replace(@value,'-',''))<2 and len(@value)>1 then
    1
    else
    0
    end
    else
    1
    end
    ) then

    1
    else
    0
    end
    )
    End


    EX

    select
    dbo.is_numeric('-234.34') as is_numeric,
    dbo.is_numeric('987.2100') as is_numeric,
    dbo.is_numeric('2.34.9612') as is_numeric,
    dbo.is_numeric('d34op') as is_numeric,
    dbo.is_numeric('27k1q-.34') as is_numeric,
    dbo.is_numeric('....') as is_numeric,
    dbo.is_numeric('-.987') as is_numeric,
    dbo.is_numeric('-') as is_numeric

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 28, 2007
    #2

  2. mattjtyler New Member

    Hi Madhivanan
    I just tried this, however it doesn't work for just '.'
    Cheers
    Matt
    mattjtyler, Jan 4, 2010
    #3

  3. Madhivanan Moderator

    [quote user="mattjtyler"]
    Hi Madhivanan
    I just tried this, however it doesn't work for just '.'
    Cheers
    Matt
    [/quote]
    Thanks. I modified the code
    Madhivanan, Jan 5, 2010
    #4

  4. Luis Martin Moderator

    Thanks for sharing.
    Luis Martin, Jan 7, 2010
    #5

  5. moh_hassan20 New Member

    The ISNUMERIC function returns 1 for some special characters like plus (+), minus (-), and valid currency symbols ($).
    example:
    select ISNUMERIC('$15') -> returns 1, because it is valid number with currency symbol
    select ISNUMERIC('15$') -> returns 0 , because it is not valid currency number


    moh_hassan20, Jan 8, 2010
    #6

  6. FrankKalis Moderator

    True, that's why Madhi enhanced it and called it is_numeric. ISNUMERIC is not really reliable and can only be used if you trust the source data it is working on.
    FrankKalis, Jan 8, 2010
    #7

  7. moh_hassan20 New Member

    So Madhivanan have to modify is_numeric() function [;)] to handle that case:
    select dbo.is_numeric('15$') as is_numeric -> returns 0
    select dbo.is_numeric('$15') as is_numeric -> returns 0

    moh_hassan20, Jan 8, 2010
    #8

  8. Madhivanan Moderator

    <P mce_keep="true">[quote user="moh_hassan20"] <P>So Madhivanan have to modify is_numeric() function&nbsp;<IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"> to handle that case:<BR>select &nbsp;&nbsp;&nbsp; dbo.is_numeric('15$') as is_numeric&nbsp; -&gt; returns 0<BR>select &nbsp;&nbsp;&nbsp; dbo.is_numeric('$15') as is_numeric&nbsp; -&gt; returns 0 <BR><BR></P><P>[/quote]</P><P>Did you read Frank's previous reply? [;)]</P>
    Madhivanan, Jan 10, 2010
    #9

  9. Madhivanan Moderator

    <P mce_keep="true">[quote user="moh_hassan20"] <P>So Madhivanan have to modify is_numeric() function&nbsp;<IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"> to handle that case:<BR>select &nbsp;&nbsp;&nbsp; dbo.is_numeric('15$') as is_numeric&nbsp; -&gt; returns 0<BR>select &nbsp;&nbsp;&nbsp; dbo.is_numeric('$15') as is_numeric&nbsp; -&gt; returns 0 <BR><BR></P><P>[/quote]</P><P>If you still dont understand, run this </P><FONT color=#0000ff size=2><P>select</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>isnumeric</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'.'</FONT><FONT color=#808080 size=2>),</FONT><FONT color=#ff00ff size=2>isnumeric</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'$'</FONT><FONT color=#808080 size=2>),</FONT><FONT color=#ff00ff size=2>isnumeric</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'12d3'</FONT><FONT color=#808080 size=2>),</FONT><FONT color=#ff00ff size=2>isnumeric</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'2,456,734'</FONT><FONT color=#808080 size=2>),</FONT><FONT color=#ff00ff size=2>isnumeric</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'$2,345'</FONT><FONT color=#808080 size=2>),</FONT><FONT color=#ff00ff size=2>isnumeric</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'1e3'</FONT><FONT color=#808080 size=2>)</P></FONT>
    Madhivanan, Jan 11, 2010
    #10

  10. moh_hassan20 New Member

    [quote user="Madhivanan"]If you still dont understand, run this [/quote]
    [:'(][:'(][:'(][:'(][:'(][:'(][:'(][:'(][:'(][:'(][:'(][:'(]
    Why are you aggressive in your reply?
    Pleaaaaaaaaase be a gentle in your reply ?
    moh_hassan20, Jan 11, 2010
    #11

  11. Adriaan New Member

    Just being cruel to be kind ...
    [A][6][A][6][A][6][A]
    Adriaan, Jan 11, 2010
    #12

  12. Madhivanan Moderator

    <P mce_keep="true">[quote user="moh_hassan20"] <P mce_keep="true">&nbsp;</P><P>[quote user="Madhivanan"]If you still dont understand, run this [/quote]</P><P><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"><IMG alt=Crying src="http://sql-server-performance.com/Community/emoticons/emotion-9.gif"></P><P>Why are you aggressive in your reply?</P><P>Pleaaaaaaaaase be a gentle in your reply ?<BR></P><P mce_keep="true">&nbsp;</P><P>[/quote]</P><P>I dont see any aggressiveness in my reply [:)]</P><P>The problem with the Formatted numbers is that they dont work in all the regions<BR>Ex 1,234,567.00 is valid expression in USA<BR>But in India it is invalid and should be expressed as 12,34,567.00<BR><BR>Also if you have junk data and want to extract only numbers from that usage of ISNUMERIC() will get you 1e3 though logically it is alphanumeric</P><P>But Frankly speaking, Frank has given all the valid points [:)]</P>
    Madhivanan, Jan 11, 2010
    #13

  13. moh_hassan20 New Member

    [quote user="Madhivanan"]The problem with the Formatted numbers is that they dont work in all the regions
    Ex 1,234,567.00 is valid expression in USA
    But in India it is invalid and should be expressed as 12,34,567.00
    [/quote]
    When i consider that comma separated numbers for a thousand separator or currency symbols, that is because most tools,excel and report generators use that numeric formats.
    So, if such format is not valid in some regions , so is_numeric() can be used [:)]
    but if such format is valid on other regions , so isnumeric() can be used


    moh_hassan20, Jan 13, 2010
    #14

  14. FrankKalis Moderator

    <p>[quote user="moh_hassan20"]</p><p>[quote user="Madhivanan"]The problem with the Formatted numbers is that they dont work in all the regions<br>Ex 1,234,567.00 is valid expression in USA<br>But in India it is invalid and should be expressed as 12,34,567.00<br>[/quote]&nbsp;</p><p>&nbsp;</p><p>When i consider that comma separated numbers for a thousand separator or currency symbols, that is because most tools,excel&nbsp; and report generators use that numeric formats.<br>So, if such format is not valid in some regions , so is_numeric() can be used <img src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" alt="Smile"><br>but if such format is valid on other regions , so isnumeric() can be used</p><p>[/quote]&nbsp;</p><p>...which boils to what I've called "trust your source data". [:)] <br></p>
    FrankKalis, Jan 13, 2010
    #15

  15. moh_hassan20 New Member

    isnumeric('2,456,734') -> 1 valid number , because it is formatted as comma separated
    is_numeric('2,456,734') -> 0 , not valid result, it should be 1
    isnumeric('$2,345') ->1 valid number , because it is currency formatted as comma separated
    is_numeric('$2,345') ->0, not valid result, it should be 1
    isnumeric('1e3') ->1 valid number , because it is 1000 , in exponent notation (for mathematician)
    is_numeric('1e3') ->0 , not valid result, it should be 1
    moh_hassan20, Jan 11, 2010
    #16

  16. FrankKalis Moderator

    Calm down, guys!
    I don't think anyone tried to offend anyone.
    FrankKalis, Jan 11, 2010
    #17

  17. FrankKalis Moderator

    [quote user="moh_hassan20"]
    isnumeric('2,456,734') -> 1 valid number , because it is formatted as comma separated
    is_numeric('2,456,734') -> 0 , not valid result, it should be 1
    isnumeric('$2,345') ->1 valid number , because it is currency formatted as comma separated
    is_numeric('$2,345') ->0, not valid result, it should be 1
    isnumeric('1e3') ->1 valid number , because it is 1000 , in exponent notation (for mathematician)
    is_numeric('1e3') ->0 , not valid result, it should be 1
    [/quote]
    The thing is that we need to be clear about the terms being used and the purpose of both functions. The ISNUMERIC definition from BOL is to say the least not precise. ISNUMERIC evaluates an expression and tries to determine whether or not this expression can be converted into one of the supported numeric type. Although BOL states as first sentence "Determines whether an expression is a valid numeric type." and only mentions this "can be converted to at least one of the numeric types" later on.
    These conversion rules most likely include taking currency symbols as well as language specific number representations into account. However, strictly speaking the first 2 expressions isnumeric('2,456,734') and isnumeric('$2,345') aren't numbers at all, because they contain non-numeric characters and have to undergo a transformation first. The 3rd expression isnumeric('1e3') is indeed a generally accepted numerical representation and thus should return 1.
    FrankKalis, Jan 11, 2010
    #18

  18. moh_hassan20 New Member

    [quote user="FrankKalis"]These conversion rules most likely include taking currency symbols as well as language specific number representations into account.[/quote]
    True Frank
    True Frank
    As we are interested for testing string as a number, the next step is to get that number.
    The data which is imported from excel may be formated in a comma separated and currency symols and even using braces() for negative number
    and we are in need to write code (as minumum as possible) to handle these cases

    DOTNET have builtin methods to handel such cases in a very simple way , and can be used as CLR procedures to convert these strings to
    numbers, even in SSIS in script component.

    Here an example of Console C# program to convert string to number
    i used the data samples of Madhivanan to prove if these are numbers or not .
    //----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    //demo program to convert comma separated, currency symbols, exponents to numbers
    using System;
    using System.Globalization;

    static void Main()
    {
    decimal number;
    string numberAsString="";
    NumberStyles style = NumberStyles.AllowThousands | // string is comma separated as thousans
    NumberStyles.AllowCurrencySymbol | //string with $ currency symbol
    NumberStyles.AllowExponent | // exponent 1e3 =1000
    NumberStyles.AllowDecimalPoint|
    NumberStyles.AllowParentheses;     // for negative number for acounting system
    try
    {

    // comma separated string
    numberAsString = "2,456,734";
    number = decimal.Parse(numberAsString, style);
    Console.WriteLine("'{0}' = {1}", numberAsString, number);

    //Curency symbol
    numberAsString ="$2,345";
    number = decimal.Parse(numberAsString, style);
    Console.WriteLine("'{0}' = {1}", numberAsString, number);

    //Parentheses , Currency symbol , which is a negative number in acounting system
    numberAsString = "($2,345)";
    number = decimal.Parse(numberAsString, style);
    Console.WriteLine("'{0}' = {1}", numberAsString, number);

    //Exponent
    numberAsString = "1e3";
    number = decimal.Parse(numberAsString, style);
    Console.WriteLine("'{0}' = {1}", numberAsString, number);

    //not valid number
    numberAsString = ".";
    number = decimal.Parse(numberAsString, style);
    Console.WriteLine("'{0}' = {1}", numberAsString, number);
    number = decimal.Parse(".", style);

    //not valid number
    numberAsString = "12d3";
    number = decimal.Parse(numberAsString, style);
    Console.WriteLine("'{0}' = {1}", numberAsString, number);
    number = decimal.Parse(".", style);


    Console.WriteLine(number);
    }
    catch (Exception ex)
    {
    Console.WriteLine("'{0}' {1}", numberAsString,ex.Message);

    }

    }
    //results
    //'2,456,734' = 2456734
    //'$2,345' = 2345
    //'($2,345)' = -2345
    //'1e3' = 1000
    //'.' Input string was not in a correct format.



    }//

    moh_hassan20, Jan 11, 2010
    #19

  19. Madhivanan Moderator

    [quote user="moh_hassan20"]
    isnumeric('2,456,734') -> 1 valid number , because it is formatted as comma separated
    is_numeric('2,456,734') -> 0 , not valid result, it should be 1
    isnumeric('$2,345') ->1 valid number , because it is currency formatted as comma separated
    is_numeric('$2,345') ->0, not valid result, it should be 1
    isnumeric('1e3') ->1 valid number , because it is 1000 , in exponent notation (for mathematician)
    is_numeric('1e3') ->0 , not valid result, it should be 1

    [/quote]
    How about other cases?
    select isnumeric('.'),isnumeric('$')

    They both retun 1
    Madhivanan, Jan 11, 2010
    #20

Share This Page