Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Unanswered: ISNUMERIC is sneaky

    sneaky, sneaky, sneaky
    ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.
    thanks, but which one??

    numeric as far as float is concerned, is not the same thing as numeric as far as money is concerned

    Code:
    create table isnumerics
    ( id integer not null identity
    , txtfld varchar(11) 
    )
    
    insert into isnumerics (txtfld) values ( '1' )
    insert into isnumerics (txtfld) values ( '937' )
    insert into isnumerics (txtfld) values ( '937.0' )
    insert into isnumerics (txtfld) values ( '$937' )
    insert into isnumerics (txtfld) values ( '$937.00' )
    insert into isnumerics (txtfld) values ( 'free' )
    insert into isnumerics (txtfld) values ( '.50' )
    insert into isnumerics (txtfld) values ( '1,000' )
    insert into isnumerics (txtfld) values ( '' )
    
    select id
         , txtfld
         , isnumeric(txtfld)
      from isnumerics
      
    1	1	1
    2	937	1
    3	937.0	1
    4	$937	1
    5	$937.00	1
    6	free	0
    7	.50	1
    8	1,000	1
    9		0
    
    
    select id
         , txtfld
         , isnumeric(txtfld)
         , case when isnumeric(txtfld) = 1
                    then cast(txtfld as money)
                    else cast(null as money)
             end as case1
      from isnumerics
    
    1	1	1	1.0000
    2	937	1	937.0000
    3	937.0	1	937.0000
    4	$937	1	937.0000
    5	$937.00	1	937.0000
    6	free	0	
    7	.50	1	.5000
    8	1,000	1	1000.0000
    9		0	  
    
    select id
         , txtfld
         , isnumeric(txtfld)
         , case isnumeric(txtfld)
                    when 1
                    then cast(txtfld as money)
                    else cast(null as money)
             end as case2
      from isnumerics
      
    1	1	1	1.0000
    2	937	1	937.0000
    3	937.0	1	937.0000
    4	$937	1	937.0000
    5	$937.00	1	937.0000
    6	free	0	
    7	.50	1	.5000
    8	1,000	1	1000.0000
    9		0	  
    
    select id
         , txtfld
         , isnumeric(txtfld)
         , case isnumeric(txtfld)
                    when 1
                    then cast(txtfld as float)
                    else cast(null as float)
             end as case2
      from isnumerics
    
      
    1	1	1	1.0
    2	937	1	937.0
    3	937.0	1	937.0 
    
    6	free	0	
    7	.50	1	0.5
    the others got "Error converting data type varchar to float"

    no, there wasn't a question here, but yes, i'd love to hear your comments
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I'd have to say it's up to the developer to know what datatype they are using. I don't believe it's the DB's responsibility to tell you what datatype the value is.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    true, but using isnumeric only will not do if a user enters a money-value that is to be inserted into a float column.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so in other words, isnumeric is not reliable if you want to cast a value to float
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ok, so we need to draft a change to MS to alter ISNUMERIC to be ISNUMERICEXCEPTIFCASTINGTOFLOAT... I think it'll fly..
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would prefer ISNUMERIC to be truthful, and a new function called ISMONEY to be available for the obviously different set of values allowed in a money field
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    I agree, I think it would even be nicer if there were functions like ISINTEGER and ISFLOAT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •