John Hass John Hass - 1 month ago 7
SQL Question

SQL Server: error converting data type nvarchar to float

I have read many articles and tried several methods but don't have any luck.

I am importing table A (whose cost is

char
to table B (which requires
float
).

What I tried:

--cast([Cost] as float)
-- cast(ISNULL([Cost],0) as float)
-- NULLIF(CAST(ISNULL([Cost],0) as float), 0)
convert(float,replace([Cost],',','') )[Cost]


Sample data

FINAL Freight Cost
1248
1248
193.79
201.56
1475.71
97.86
97.86
97.86
125.49
97.86
447.83
450
492.99
450


And I still get this error:


Error converting data type nvarchar to float


Update:

In addition, I am not sure how to modify the existing code based on the answers;

The existing code structure

1. creat table B
2. insert into B () select[DestAddress], [COST] from A


I also tried
CAST(test AS FLOAT) AS CastedValue





Why
float
? The dataset will be sent to an optimization algorithm which requires
float
. Thanks for pointing it out though.

Answer

This works for me?

DECLARE @tbl TABLE(test NVARCHAR(100));
INSERT INTO @tbl VALUES
 ('1248')
,('1248')
,('193.79')
,('201.56')
,('1475.71')
,('97.86')
,('97.86')
,('97.86')
,('125.49')
,('97.86')
,('447.83')
,('450')
,('492.99')
,('450');

SELECT *
     ,CAST(test AS FLOAT) AS CastedValue
FROM @tbl;

But the main question is: Why?

Hint 1: Float is the wrong type for this!

From the column name I take, that you are dealing with costs. The FLOAT type is absolutely to be avoided here! You should use DECIMAL or specialised types to cover money or currency values...

Hint 2: NVarchar is the wrong type for this!

And the next question is again: Why? Why are these values stored as NVARCHAR? If ever possible you should solve your problem here...

UPDATE

You edited your question and added this

insert into B () select[DestAddress], [COST]

I do not know the target table's column names, but this should work

INSERT INTO B(ColumnForAddress,ColumnForCost)
SELECT CAST([COST] AS FLOAT),[DestAddress] FROM YourSourceTable

UPDATE 2

After all your comments I'm pretty sure, that there are invalid values within your numbers list. Use ISNUMERIC or - if you are using SQL-Server-2012+ even better TRY_CAST to find invalid values.

Comments