SQL : Error converting data type nvarchar to float

Question

I want to convert nvarchar data to float type.

In my case I have SalesValue column and I used this command

UPDATE Overseas 
SET SalesValue = CONVERT(FLOAT, REPLACE([SalesValue],',','') )

My table has values like

But I get an error

SQL : Error converting data type nvarchar to float.

How can I solve this problem ?

Are these money amounts? Would not recommend using FLOAT to begin with. Use DECIMAL or MONEY. And the issue is not going to be with the decimal values you gave as examples. It's going to be with the values that cannot be converted to decimals (like dollar signs $). — Jacob H, Jun 05 '17 at 20:58
Those values are all convertible to the float data type. SELECT CAST('201.01' AS FLOAT) SELECT CAST('40.50' AS FLOAT) SELECT CAST('215.12' AS FLOAT) SELECT CAST('550' AS FLOAT) SELECT CAST('304.201' AS FLOAT) — Tarzan, Jun 05 '17 at 20:59
@Tarzan i give u example i want all my data in this table converting to float — 3mr, Jun 05 '17 at 21:00
Most likely you have data in the table that cannot be converted to a number. Like other characters ($, %, etc.). — Jacob H, Jun 05 '17 at 21:01
you have some data beyond comma that isn't convertable to float. like a space, enter etc... in the sales value column. and why float?. Float is imprecise! decimal with (15,5) or something would be more precise — xQbert, Jun 05 '17 at 21:01
The problem isn't the commas... please reread everything here. — Jacob H, Jun 05 '17 at 21:03
i just want to make math operations in this numbers and nvarchar didn't support that — 3mr, Jun 05 '17 at 21:04
The problem is the datatype in the database is not correct for doing math. This has resulted in non-numeric values making it into the column thus math can't be done. You need to sanitize the data first to clean up those values which are not numeric, then change the datatype of the column to decimal to support your needs and prevent bad data from getting in again, then you can do the math. However this implies that the column of Salesvalue should only allow numeric values. — xQbert, Jun 06 '17 at 12:36
If you EVER need to do math on it, store it as a number If you'll NEVER do math on it store it as string If it needs to do both, you need two columns Except for dates... store dates as dates period and use date functions on them... not string {shudder} functions! and AutoIncrements can be numbers (though we should never do math on them) @xQbert -- this shall henceforth be referred to as "xQbert's razor" — xQbert, Jun 06 '17 at 12:36

score 2 · Answer 1 · answered Jun 05 '17 at 20:57

2

You should find the values that do not match. In SQL Server 2012+, you can use try_convert(). That is not available. So, how about this?

SELECT SalesValue
FROM Overseas
WHERE SalesValue LIKE '%[^0-9,.]%' OR
      SalesValue LIKE '%[.,]%[.,]%';

I think that covers the obvious irregularities: a character that is not numeric or two (or more) decimal points.

answered Jun 05 '17 at 20:57

Gordon Linoff

1,242,037
58
646
786

@3mr . . . You have to run the query to figure out what the bad values are. Then deal with them accordingly. – Gordon Linoff Jun 05 '17 at 23:32
I did as u said but i load my data usually and dynamically from excel file .. what need to now where is the bad values manually ? – 3mr Jun 06 '17 at 11:41
1

@3mr . . . Excel does not enforce types, so anything can be being imported. – Gordon Linoff Jun 06 '17 at 11:48
so i got the bad values .. what then ? – 3mr Jun 06 '17 at 11:49
1

@3mr . . . I would suggest that you ask another question and show the actual values that are failing. – Gordon Linoff Jun 06 '17 at 11:55
what is this flaws ? – 3mr Jun 06 '17 at 13:20

score 0 · Answer 2 · answered Jun 05 '17 at 22:16

It sounds like to me your data still has something that is non numerical. I'm hoping that your application side had done a pretty good job at cleaning your data before inputting and the problem is probably you have a '$' in one or more of your fields. Cast will fail when you have a non numerical char other then '.' in it (as you probably know which is why you removed ','). I ran the below script to test this.

declare @myFloat float;
declare @test1 nvarchar(10) = '145.88';
declare @test2 nvarchar(10) = '4,145.88';
declare @test3 nvarchar(10) = '$4,145.88';

SELECT ISNUMERIC(@TEST3)
set @myFloat = CONVERT(FLOAT, REPLACE(@test1,',','') );
select @myFloat;

set @myFloat = CONVERT(FLOAT, REPLACE(@test2,',','') );
select @myFloat;

--THIS WILL FAIL
set @myFloat = CONVERT(FLOAT, REPLACE(@test3,',','') );
select @myFloat;
--THIS WILL NOT FAIL
set @myFloat = CONVERT(FLOAT, REPLACE(REPLACE(@test3,',',''),'$','') );
select @myFloat;

You can try running the below script on the column in question to see which columns you are having a problem with:

--run this on your table
SELECT SalesValue
FROM Overseas
WHERE ISNUMERIC(REPLACE(SalesValue,',','')) = 0

--test sample
/*
insert into #myTable
values ('145.88'),
       ('4,145.88'),
       ('$4,145.88'),
       ('$4,145.88%'); 

SELECT *
FROM #myTable
WHERE ISNUMERIC(REPLACE(amounts,',','')) = 0 
--WHERE ISNUMERIC(REPLACE(REPLACE(amounts,',',''),'$','')) = 0 --this will remove results with $ also
*/

So your fix will be to simply change the line you provided to:

UPDATE Overseas SET SalesValue = CONVERT(FLOAT, REPLACE(REPLACE([SalesValue],',',''),'$','') )

Unless you found other chars in the results of prior script.

I haven't $ sign in my values as i said .. i got my data from excel sheet and i think this is the problem .. — 3mr, Jun 06 '17 at 11:43
Check your code with scientific notation... i.e. `'1e4'` as @test — S3S, Jun 06 '17 at 13:00
I agree with scsimon. Excel tries to 'help' by determining data types based of a sampling of column data. At my job, I have an SSIS package that looks at cells in an excel doc. I found in testing and prod where when the workbook is not preformatted to 'text' for all the cells, or if you copy and paste from another source(encoding issues), my package would not read the data correctly. @3mr todo: get a blank workbook, select all, format cells as text, paste in your data and try again. — CPearson, Jun 06 '17 at 19:48
@3mr, did you ever run the script to check for any non numerical rows in your data? SELECT SalesValue AS [BAD VALUE], * FROM Overseas WHERE ISNUMERIC(REPLACE(SalesValue,',','')) = 0 — CPearson, Jun 06 '17 at 19:50

score 0 · Accepted Answer · answered Jun 06 '17 at 12:41

0

1st cast value by using below query and then update normally

 SELECT
          case when  ISNUMERIC([SalesValue])=1
          then CAST([SalesValue] AS FLOAT) else 0 end AS CastedValue)
      FROM your_table_name

answered Jun 06 '17 at 12:41

Zaynul Abadin Tuhin

31,407
5
33
63

Try your code where `SalesValue = '$'` or `SalesValue = '1e4'`. Your method is flawed and you would benefit from reading [this post](https://stackoverflow.com/a/7400335/6167855) and it's underlying references. – S3S Jun 06 '17 at 12:56

score 0 · Answer 4 · answered Jun 06 '17 at 13:35

This will get you closer than ISNUMERIC()

declare @table table (SalesValue varchar(16))
insert into @table
values
('1e4'),
('$'),
('134.55'),
('66,9897'),
('14')

select
    SalesValue
    ,case 
        when SalesValue NOT LIKE '%[^0-9,.]%' 
        then convert(decimal(16,4),replace(SalesValue,',','.'))
    end
from 
    @table

SQL : Error converting data type nvarchar to float

4 Answers4