Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Conversion Error Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
Points: 20, Visits: 55
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.
Post #1433622
Posted Thursday, March 21, 2013 2:25 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:37 AM
Points: 23, Visits: 602
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"
Post #1433629
Posted Thursday, March 21, 2013 9:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 5:31 PM
Points: 238, Visits: 2,221
Using isnumeric wont really give you an accurate answer.

Please see this article for an explanation
http://www.sqlservercentral.com/articles/IsNumeric/71512/
Post #1434118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse