Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Convert varchar to float, decimal

    Hello,

    I have a field in my database that is stored as varchar. The values are usually contain a decimal, and should have really been a float or decimal. In order for me to do analytics in my BI environment, I need to convert this to a float or decimal.
    eg of values.
    10.00
    20.00
    0.00
    15.00
    or could be missing

    when I use cast(value as float) or cast(value as decimal(9,2)) or convert(float, value) I get an error


    Msg 8114, Level 16, State 5, Line 2
    Error converting data type varchar to numeric.


    any help will be greatly appreciated.

    Thanks,

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT id, aColumn, 
    	CASE WHEN ISNUMERIC(aColumn) = 1
    		THEN CAST(aColumn AS FLOAT) 
    		ELSE NULL 
    	END as aFloat
    FROM #DaTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You probably have a few rogue characters running around. Here is an example of how the ISNUMERIC function can trip you up...
    Code:
    select convert(float, '$10.00')
    select isnumeric ('$10.00')
    ISNUMERIC will return true on a value that can be converted to some numeric format, which includes money, and scientific notation (I'm looking at you, Excel).

Posting Permissions

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