August 4, 2011 at 7:42 am
Hello,
I thought I'd done this before but don't remember the remedy to the error. I'm trying to convert part of a string to a decimal value but I get the "Arithmetic overflow error converting nvarchar to data type numeric" error. I run a check to see if the string contains a numeric in that position and I get the appropriate response but when I try to perform the conversion it errors.
Below is a sample, any assistance is appreciated.
Regards.
SELECT
L.JOBDESCRIPTION
, CASE
WHEN ISNUMERIC(SUBSTRING(L.JOBDESCRIPTION, 0,6))=1 THEN CONVERT(DECIMAL,SUBSTRING(L.JOBDESCRIPTION,0,6))
WHEN ISNUMERIC(SUBSTRING(L.JOBDESCRIPTION, 0,6))=0 THEN 'FALSE'
END NUMERICTEST
FROM DBO.LABORLINFO L
WHERE L.LABORINFODEFID = 1004
AND ISNUMERIC(SUBSTRING(L.JOBDESCRIPTION, 0,6))=1
ORDER BY NUMERICTEST
August 4, 2011 at 7:45 am
You have mixed datatypes in your case statement. The first case will be a decimal and then your case else is trying to put a string in that column. You may need to look at finding a value that can substitute for your 'FALSE' like 0 or -1 maybe?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2011 at 8:16 am
Thanks Sean,
I found a data quality issue that the isnumeric was ok with the convert function was not. some values contained a '-' minus sign and the isnumeric function was quite accepting of that but convert function could not handle.
Thanks again.
August 4, 2011 at 8:29 am
larry.powell (8/4/2011)
Thanks Sean,I found a data quality issue that the isnumeric was ok with the convert function was not. some values contained a '-' minus sign and the isnumeric function was quite accepting of that but convert function could not handle.
Thanks again.
Totally agree!
We have code to convert varchar to bigint, and we use isnumeric to check the data quality of the varchar value, but this guy gave us a lot of headache:
3d-222135018
select isnumeric('3d-222135018')
1
August 4, 2011 at 8:53 am
The IsNumeric function has been the cause of a lot of failed code over the years. It is really not a good way to accurately determine if a value is numeric. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply