Convert different scientific notations to numeric-CodePudding

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.

CodePudding user response：

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