Error converting data type nvarchar to float

Question

Post reply

Error converting data type nvarchar to float

yuvipoy

SSChampion

Points: 10469
More actions
December 15, 2016 at 6:03 am

#319754

Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am converting to float, but getting error "Error converting data type nvarchar to float"
there are 10 million records in it, dont know which record is causing problem. how to sought it .
Advance thanks!
December 20, 2016 at 9:21 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 1

Hi ,

normally its working .

better you post some sample data or check any chacters or (comma , instead of dot . found) ..! then only we can able to fix your real-time bugs.

use tempdb

create table test1(number nvarchar(100))

insert into test1 values('120450,454852'),('120450.454852'),('14520450.454852'),('100000.45454852')

-- select number , cast(number as float) + 1.0 as conversion from test1 -- > Error

select number , cast(replace(number,',','.') as float) + 1.0 as conversion from test1 -- > Solution

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

yuvipoy (12/15/2016)
Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am converting to float, but getting error "Error converting data type nvarchar to float"
there are 10 million records in it, dont know which record is causing problem. how to sought it .
Advance thanks!

SELECT mycolumn, x.mycolumnAsFloat

FROM mytable

CROSS APPLY (SELECT mycolumnAsFloat = TRY_CONVERT(float,mycolumn) ) x

WHERE x.mycolumnAsFloat IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sgmunson SSC Guru Points: 110639 More actions · Answer 3

yuvipoy (12/15/2016)
Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am converting to float, but getting error "Error converting data type nvarchar to float"
there are 10 million records in it, dont know which record is causing problem. how to sought it .
Advance thanks!

You can at least try to find the errors this way:

SELECT *

FROM mytable

WHERE ISNUMERIC(mycolumn) = 0

This may not catch everything, but as you're looking to convert to float, it might well catch the vast majority. If you were on a higher version of SQL Server, such as SQL 2012, you could use TRY_CONVERT(float, mycolumn), which would return NULL values where mycolumn could not be converted. You'd also have to only check non-null values of mycolumn, but I think you get the idea.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110639 More actions · Answer 4

ChrisM@Work (12/15/2016)
yuvipoy (12/15/2016)
Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am converting to float, but getting error "Error converting data type nvarchar to float"
there are 10 million records in it, dont know which record is causing problem. how to sought it .
Advance thanks!
SELECT mycolumn, x.mycolumnAsFloat
FROM mytable
CROSS APPLY (SELECT mycolumnAsFloat = TRY_CONVERT(float,mycolumn) ) x
WHERE x.mycolumnAsFloat IS NULL

Chris,

That's a SQL 2012 feature, and this is a 2008 forum...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

sgmunson (12/15/2016)
ChrisM@Work (12/15/2016)
yuvipoy (12/15/2016)
Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am converting to float, but getting error "Error converting data type nvarchar to float"
there are 10 million records in it, dont know which record is causing problem. how to sought it .
Advance thanks!
SELECT mycolumn, x.mycolumnAsFloat
FROM mytable
CROSS APPLY (SELECT mycolumnAsFloat = TRY_CONVERT(float,mycolumn) ) x
WHERE x.mycolumnAsFloat IS NULL
Chris,
That's a SQL 2012 feature, and this is a 2008 forum...

Oh cr@p, thanks Steve.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

yuvipoy SSChampion Points: 10469 More actions · Answer 6

Anandkumar-SQL_Developer (12/15/2016)
Hi ,
normally its working .
better you post some sample data or check any chacters or (comma , instead of dot . found) ..! then only we can able to fix your real-time bugs.

Thanks Anandkumar..!! this are some preliminary checks which done before posting...

Thanks all.

Select Convert(float as mycolumn) from table -->gives error

Select TRY_CONVERT(FLOAT, mycolumn) from table --> did not give error when tried in SQL server 2012.

Wondering what is the difference and why SQL server 2008 convert(float ) did not worked 🙁 , these many days it was working ?

SELECT *

FROM mytable

WHERE ISNUMERIC(mycolumn) = 0

does not return anything -- Zero rows

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

Both CONVERT and TRY_CONVERT function converts the expression to the requested type. But if the CONVERT function fails to convert the value to the requested type then raises an exception, on the other hand if TRY_CONVERT function returns a NULL value if it fails to convert the value to the requested type.

Below example demonstrates this difference:

SELECT CONVERT(float, '.') AS 'CONVERT Function Result'

SELECT TRY_CONVERT(float, '.') AS 'TRY_CONVERT Function Result'

Furthermore, ISNUMERIC() is not a very good function to determine whether data is a proper numeric or not

Below example demonstrates this:

Select ISNUMERIC('.') AS 'IsNumeric Function Result' --- Will be considered as numeric

Select CONVERT(float, '.') AS 'CONVERT Function Result' --- Will raise an error

First you need to identify what is causing values are causing this for this you can use ChrisM@Work query. Once you identify those data anomalies it will be easy for you to rectify them.

Hope it helps

yuvipoy SSChampion Points: 10469 More actions · Answer 8

No actually i guess there is an issue in SQL server 2012 which ever data column which is having as float or convert(float ) has an issue,where as in SQL server 2016 there is no issue.

Since my origin database is SQL server 2008R2 i posted here..

Which ever data is there in SQL server 2008R2 having float column when ran on SQL server 2012 there is an issue, where as same in SQL server 2016 there is no issue. tested more than 5 times 🙂 🙂

sgmunson SSC Guru Points: 110639 More actions · Answer 9

yuvipoy (12/16/2016)
No actually i guess there is an issue in SQL server 2012 which ever data column which is having as float or convert(float ) has an issue,where as in SQL server 2016 there is no issue.
Since my origin database is SQL server 2008R2 i posted here..
Which ever data is there in SQL server 2008R2 having float column when ran on SQL server 2012 there is an issue, where as same in SQL server 2016 there is no issue. tested more than 5 times 🙂 🙂

Are you sure you aren't just getting NULL values from conversions that fail but don't create an exception? Also, can you detect the difference between a NULL generated because the field to be converted has a NULL value and a NULL generated because of a failed conversion?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)