0
votes

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.

3
Try without replacing the dots? - Lasse V. Karlsen
Replace [FINAL Freight Cost] with LTRIM(RTRIM([FINAL Freight Cost])) - Hadi
What are you trying to do in your own words? - juergen d
My guess is that you have a row in your source that has some value that is not actually a valid number. This is so often the case when people store numbers as characters. - Sean Lange
@Shnugo Tried. There is a "n/a" in 100k rows. Solved. Thanks!!!!!! - Jill Clover

3 Answers

4
votes

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.

1
votes

Use Convert function , it works smoothly even adding spaces , so there is no need for using Ltrim or Rtrim!

Example:

select convert(float,' 492.99    ') + 1 as converted

Result:

Converted
---------
493.99
-1
votes
FORMAT(ROUND(cast(Total as float), 2), 'C') AS GrandTotal