Convert varchar to decimal using isnumeric without throwing an exception in SQL Server 2008

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

Answers


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)


Need Your Help

Getting same contacts multiple times rather than multiple contacts

ios objective-c uitableview data-structures abaddressbook

Hi I am new to iphone Application Development, but this question is more of coding related.I am using a tableview to display users name(textLabel) with phoneNumbers(detailtextLabel) using Addressbo...

Download data from website after selecting dropdowns and searching

batch-file website download

I am not a pure programmer but have good basic knowledge of programming. I am trying to automate a data download process (from a website) which I have to do every month. Following is the website wh...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.