March 21, 2013 at 2:09 am
I have a staging data which holds all data as NVARCHAR, I am now loading from the staging table to specific tables where I convert various fields to numerics, but I'm getting a conversion error. Is there any easy way of finding which row is causing the problem. I've searched through the forum for this error but couldn't find anything that matches my specific needs.
March 21, 2013 at 2:25 am
Next piece of code will not give you the rows, but it contains a hint on what to use to find those rows 😉
WITH ListValues AS ( SELECT N'123' AS Value UNION SELECT N'ABC' UNION SELECT '12AB' )
SELECT Value + ' : ' +
, CASE ISNUMERIC(Value)
WHEN 1 THEN 'IS NUMERIC'
WHEN 0 THEN 'IS NOT NUMERIC'
END
FROM ListValues
123 : IS NUMERIC
12AB : IS NOT NUMERIC
ABC : IS NOT NUMERIC
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
March 21, 2013 at 9:10 pm
Using isnumeric wont really give you an accurate answer.
Please see this article for an explanation
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply