11

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?

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
HM8689
  • 143
  • 1
  • 1
  • 10

4 Answers4

17

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for the suggestion but that doesn't work. Gives the same error message – HM8689 Feb 15 '18 at 12:02
  • 1
    @HM8689 of that, I am doubtful. `TRY_CONVERT` won't generate that error; as if the conversion fails *`NULL`* is returned. perhaps you could edit your post with your (new) query? – Thom A Feb 15 '18 at 12:04
  • @HM8689, you'll need to post your actual query. Depending on the particulars of you query and execution plan, SQL Server might attempt to convert the value before `TRY_CONVERT` is evaluated. – Dan Guzman Feb 15 '18 at 12:05
  • Thank you- this is working fine! There was another conversion as well which had to be coded as try_convert to make it work. – HM8689 Feb 15 '18 at 12:16
  • 1
    Though `try_convert` works from SQL Server (starting with 2012) .i'm getting an error `Msg 195, Level 15, State 10, Line 2 'TRY_CONVERT' is not a recognized built-in function name.` when i tried with an example : 'SELECT TRY_CONVERT(INT, 'A100.000') AS ValueInt` – Mahesh.K Feb 15 '18 at 12:40
  • Whereas `TRY_PARSE` and `TRY_CAST` functions are working fine :( – Mahesh.K Feb 15 '18 at 12:42
  • @Mahesh.K . . . That doesn't quite make sense, but you can use `try_cast()` instead. – Gordon Linoff Feb 15 '18 at 13:27
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
Prabhat G
  • 2,974
  • 1
  • 22
  • 31
1

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

SChowdhury
  • 163
  • 1
  • 11
1

You can try the convert function in SQLServer:

select Convert(float, "column_name") from TABLE

Empty fields are returned as 0.

esaintpi
  • 11
  • 2