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
  2. mattjtyler New Member

    Hi Madhivanan
    I just tried this, however it doesn't work for just '.'
    Cheers
    Matt
  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
  4. Luis Martin Moderator

    Thanks for sharing.
  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


  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.
  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

  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>
  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>
  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 ?
  11. Adriaan New Member

    Just being cruel to be kind ...
    [A][6][A][6][A][6][A]
  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>
  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


  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>
  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
  16. FrankKalis Moderator

    Calm down, guys!
    I don't think anyone tried to offend anyone.
  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.
  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.




    }//

  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

Share This Page