Convert nvarchar to numeric in MSSQL Server

Question

I have a nvarchar column with a list of entries such as (including NULLs)-

-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

I want to convert this to a numeric field so that I can use this field to do some calculations. However, I get the following error message-

Error converting data type nvarchar to float.

Can someone please help?

Answer 1

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 col
from t
where 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.

Answer 2

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

Answer 3

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