Post reply

ISNUMERIC and CONVERT inconsistency

  • October 14, 2002 at 4:06 am

    #80148

    Watch out for this one in SQL7!

    select isnumeric('4,668.90')

    ...returns 1 as it is numeric

    select convert(numeric, '4,668.90')

    ...fails because the string is not numeric (the comma)

  • October 14, 2002 at 1:10 pm

    #438530

    Sorry no server near by but try CONVERT(numeric(12,3), '4,668.90') see the thing with ISNUMERIC only check it the string or value can be interpreted as a valid integer, floating point number, money or decimal type. Might also try money instead of numeric as I believe the whole ISNUMERIC thing is a known deal and sorta a misrepresentation of what a 1 means.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • October 14, 2002 at 2:30 pm

    #438540

    He's right commas are only valid for money datatypes, this also applies when outputing values to strings the style property only works for money datatypes,

    i.e.

    select CONVERT (varchar(10),4343.00,1)

    select CONVERT (varchar(10),cast(4343.00 as money),1)

    gives

    ----------

    4343.00

    ----------

    4,343.00

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 3 posts - 1 through 2 (of 2 total)

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