I have a table with a varchar field that contains values with the following structures:
1.69665589928627E-06
3.57807129940753E-07
4.77823428809643E-08
1.58324837684631E-12
1.57160684466362E-13
0.0062
10.1595896112714
0.0505828946151305
0.739
0
How to convert these values to Numeric(12,6)?
I tried using CAST:
SELECT CAST (NUM_AREA AS NUMERIC(12,6)) FROM APP
SELECT CAST(CAST(NUM_AREA AS FLOAT) AS NUMERIC(12,6)) FROM APP
But the following error is generated:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
>Solution :
Being a nvarchar, I suspect you have some bogus data.
Use try_convert() which will return a NULL rather than throwing a error
Example or dbFiddle
Declare @YourTable Table ([Num_Area] nvarchar(50)) Insert Into @YourTable Values
('1.69665589928627E-06')
,('3.57807129940753E-07')
,('4.77823428809643E-08')
,('1.58324837684631E-12')
,('1.57160684466362E-13')
,('0.0062')
,('10.1595896112714')
,('0.0505828946151305')
,('0.739')
,('0')
,('Not A NUmber') -- WOULD THROW AN ERROR USING CONVERT or CAST
,('1.57160684466362E-55') -- REALLY SMALL NUMBER, YOU'LL JUST GET ZERO
Select *
,try_convert(numeric(12,6),try_convert(float,Num_Area))
from @YourTable
--**To Identify the Problem Rows**
Select *
From @YourTable
Where try_convert(float,Num_Area) is null
and Num_Area is not null
Results
