Convert nvarchar to numeric in MSSQL Server
Your data would appear to have values are not valid numeric values. Use try_convert():
select try_convert(numeric(38, 12), col)This will return NULL if there is a failure in the conversion.
You can find the values that fail the conversion by doing:
select colfrom twhere try_convert(numeric(38, 12), col) is null and col is not null;You need to use try_convert() wherever you reference the column as a numeric. Converting in the select only applies to the select.
Just to demonstrate Gordon's method in case OP needs:
create table #test(val nvarchar(100));insert into #test values('-1.00000'),('0.000000'),('0.010000'),('0.100000'),('0.500000'),('00000000'),('1.000000'),('1.500000'),('10.00000'),('10.50000'),('100.0000'),('1000.000'),('1001.000'),('1006.000'),(NULL),(NULL)select val, TRY_CONVERT(numeric(38,12),val) as converted_val from #test
Use try_convert(numeric(38,5), [ColumnName])
This will return NULL when it cannot convert the string. If it can, it will be able to convert it to a numeric number