1

I have a varchar field that should always contain a numeric (decimal) value when a filter is applied. But I get "Error converting data type varchar to numeric" when i run sql like this:

SELECT CAST(A.text AS numeric(38,16)) 
FROM Answers A
INNER JOIN Questions Q ON Q.ID = A.QuestionID
WHERE Q.Text = 'Rating'
AND isnumeric(A.text) = 1

There is only one value that passes the isnumeric test but fails the cast:

DECLARE @text varchar(100)
SET @text = '2.83417869359255E-02'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS NUMERIC(38,16)) ELSE NULL END

How should I ensure that values like this are handled? At the end of the day I want to count all values that are less than 5, so numbers like '2.83417869359255E-02' could be rounded to zero. I have also run a query to examine the values in the table together with the length of the varchar with these results:

MaxValue             MinValue             LenVarChar
-------------------- -------------------- -----------
0                    0                    1
10                   10                   2
2.2                  9.2                  3
3.55                 6.32                 4
5.453125             5.453125             8
2.79989361763        9.47216796875        13
2.089115858078       9.132080078125       14
1.1529632806778      9.8538990020752      15
0.64174896478653     9.83681106567383     16
0.111961431801319    0.991760730743408    17
2.83417869359255E-02 2.83417869359255E-02 20
Colin
  • 22,328
  • 17
  • 103
  • 197

1 Answers1

1

Not storing numbers as strings would be my preferred solution but you could always check for D or E and then cast to float first.

DECLARE @text VARCHAR(100)

SET @text = '2.83417869359255E-02'

SELECT
    CASE
    WHEN ISNUMERIC(@text) = 1
    THEN      CASE 
              WHEN @text LIKE '%[DE]%' 
              THEN CAST(CAST(@text AS FLOAT) AS NUMERIC(38, 16))
              ELSE @text
              END 
    END

There are still potentially problematic inputs however such as $ (which passes the ISNUMERIC test but would require a cast to money)

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Actually for my purposes (finding values less than 5) I can convert them all to float. And at some point in the future I will create a decimal field in the 'Answer' table for storing decimal answers then use this technique to populate it from the varchar field. And for anyone like me who needs to understand floats, decimals and numerics better here's a link - http://stackoverflow.com/q/1056323/150342 – Colin Jan 23 '13 at 10:49