Numeric or Not Numeric

  • AmolNaik

    SSCarpal Tunnel

    Points: 4767

    Comments posted to this topic are about the item Numeric or Not Numeric

    Amol Naik

  • SQLRNNR

    SSC Guru

    Points: 281210

    nice question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • David Todd-242471

    Right there with Babe

    Points: 797

    Hi,

    Interesting question.

    But this code - based on the question - fails with Error converting data type varchar to float.

    [font="Courier New"]if( select isnumeric( @PrincipalAmount )) = 1

    select convert( float, @PrincipalAmount )

    else

    print 'Sorry'

    ;[/font]

    That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to. It would be better imho if isnumeric was more robust. What is the point of using it, if will have to use exception handling anyway to catch errors that isnumeric missed?

    Cheers

    David

  • vk-kirov

    SSCertifiable

    Points: 7686

    ISNUMERIC() function always ignores commas while validating data

    The question is good, but the explanation is wrong. In some cases commas are not ignored:

    SELECT ISNUMERIC('-1') -- the result is 1

    SELECT ISNUMERIC(',-1') -- the result is 0, this shows that the comma is not ignored

    Another example:

    SELECT ISNUMERIC('') -- the result is 0

    SELECT ISNUMERIC(',') -- the result is 1; if ISNUMERIC() ignored the comma,

    -- the result would be 0

    David Todd-242471 (2/21/2010)


    But this code - based on the question - fails with Error converting data type varchar to float.

    if( select isnumeric( @PrincipalAmount )) = 1

    select convert( float, @PrincipalAmount )

    else

    print 'Sorry'

    ;

    That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to.

    Float is not the only numeric data type 🙂 The conversion to money works fine.

  • ziangij

    SSCertifiable

    Points: 6958

    That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to

    Float is not the only numeric data type 🙂 The conversion to money works fine.

    conversion to money alone works... nothing else viz. float, int, numeric works...

  • This was removed by the editor as SPAM

  • Fal

    SSCrazy

    Points: 2871

    ISNUMERIC is more general than CONVERT. The following is also valid (in 2008):

    SET @PrincipalAmount = '100e2'

    It will convert to float, but not money. With the QotD I was more concerned about the decimal place with no trailing zeroes.

    S.

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    We already had a similar question before.

    If a number string can be converted to any numeric type, ISNUMERIC returns 1.

    Among the known "issues" with this are:

    money: 1,,,,,,1 is a valid money value (obviously it does not make sense though)

    float: 1e1 is a valid float value

    float: 1d1 is a valid float value

    IsNumeric is consistent in the way that it verifies that it can convert to any SQL Server numeric data type (which includes monetary datatypes & float).

    So blame it on the conversion functions instead:)

    Best Regards,

    Chris Büttner

  • kaspencer

    SSCarpal Tunnel

    Points: 4244

    Good question. And it also raises a further point in my mind ...

    Is there a need to standardise numerical representation across every locale ?

    I ask this because, in some locales, the number expressed as

    700,000

    could represent

    700 [decimal point] 000

    that is, 700 expressed to three decimal places.

    In other locales, it would be

    700 [thousands separator] 000

    I have noticed a mixture of decimal point indicators on invoices these days, and it certainly isn't unusual to get one using the comma [,] as a decimal point rather than the more logical full-stop [.].

    Kenneth Spencer

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • RedLlewy

    Ten Centuries

    Points: 1024

    Good Question:

    Also

    DECLARE @PrincipalAmount VARCHAR(15)

    SET @PrincipalAmount = '£700,000'

    SELECT ISNUMERIC(@PrincipalAmount)

    returns 1 as does

    DECLARE @PrincipalAmount VARCHAR(15)

    SET @PrincipalAmount = '$700,000'

    SELECT ISNUMERIC(@PrincipalAmount)

    See http://msdn.microsoft.com/en-us/library/ms188688.aspx for other recognised currency symbols


    RedLlewy
    "The Plural of Anecdote is not Data"

  • Dietmar Weickert

    SSCrazy

    Points: 2258

    kaspencer (2/22/2010)


    Good question. And it also raises a further point in my mind ...

    Is there a need to standardise numerical representation across every locale ?

    I ask this because, in some locales, the number expressed as

    700,000

    could represent

    700 [decimal point] 000

    that is, 700 expressed to three decimal places.

    In other locales, it would be

    700 [thousands separator] 000

    I have noticed a mixture of decimal point indicators on invoices these days, and it certainly isn't unusual to get one using the comma [,] as a decimal point rather than the more logical full-stop [.].

    Kenneth Spencer

    You are right, in German the comma separates integral and decimal part, and the point separates the thousands.

    Makes both formatting and scanning numbers real fun. At least Windows helps, as it covers the problem in its Regional and Language Settings in the Control Panel, and most libraries take these settings into account.

    Currency signs, first day of the week, the sequence of day month and year in a standard date and other often neglected differences may become a pain in the ... as well. :rolleyes:

    Best regards,
    Dietmar Weickert.

  • alastors

    SSC Enthusiast

    Points: 155

    in Argentina (and all Hispanic countries, AFAIK) comma separates the integral and decimal part, and the point separates the thousands

  • crashdan

    SSCommitted

    Points: 1940

    Fal (2/22/2010)


    ISNUMERIC is more general than CONVERT. The following is also valid (in 2008):

    SET @PrincipalAmount = '100e2'

    It will convert to float, but not money. With the QotD I was more concerned about the decimal place with no trailing zeroes.

    S.

    i found out the "e2" issue by accident. One of our customers likes using those in conjunction with numbers for locations ... WTH

  • vk-kirov

    SSCertifiable

    Points: 7686

    kaspencer (2/22/2010)


    Good question. And it also raises a further point in my mind ...

    Is there a need to standardise numerical representation across every locale ?

    Is there a need to standardize systems of measurement, alphabets, languages etc? This is what makes countries and cultures different from each other 🙂

  • TomThomson

    SSC Guru

    Points: 104772

    A nice question.

    It's pretty appalling that 8% of people chose the "error message" answer, though.

    I'm much less surprised about the 17% who got it wrong to date by picking 0, as it seems (until you think about it properly) reasonable to assume that isNumeric is locale dependent and should return 0 in locales where the representation, with fraction and thousands seperators, of 700000 is '700.000,'. In fact it isn't locale dependent - whichever way round you use ',' and '.' it is acceptable to isNumeric in all locales - and also the conversion functions accept both notations instead of just the one applicable in the current locale and it wouldn't in fact be reasonable to do make it locale dependent. On the contrary it would be unreasonable and significantly reduce usability - for example people in the UK need to be able to process electronic invoices from UK, Ireland and the US and also from mainland Europe - so two diffent separator conventions have to be supported.

    Tom

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply