Convert nvarchar to numeric in MSSQL Server Convert nvarchar to numeric in MSSQL Server sql sql

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