Cannot cast nvarchar to float

Question

I have a weird problem.

In my query I select 5 columns which two of them are nvarchar in the form of numeric (only contains number and precision point), and other three are uniqueIdentifier and nvarchar.

I want to cast these two columns to Float in my select statement but I'm getting an error

Cannot cast nvarchar to float.

I checked the format of all these values many many times. Trust me, they are fine.

But when I just select these two columns and cast them to float the query runs successfully.

I appreciate any help on this subject.

I can paste the query here too. but the whole query is more then 100 lines and this will be frustrating to write and read!

So, instead of posting a 100 line query, try to reduce the size of the query down to something manageable, but that still exhibits the problem. Often, in the course of trying to do this, you'll gain new insights into the underlying issue - but as the question currently is, I doubt we can help you. — Damien_The_Unbeliever
Trust me, the optimiser doesn't have a vendetta against you. Your debugging must have accidentally 'proved' something is fine, when it's not. Have you tried select cast(x as float) from yourTable? If that fails for either column, you know atleast one value is not compatible with that cast. Then try select top 100 x from yourTable order by x asc and then desc; often the problem values are at the extremes. — MatBailie
I've tried this. "select cast(x as float) from yourTable" works well. the problem rises when I select some another columns beside those ones. — Ashkan
@AshkanAleAli, you might be suspecting wrong columns for the cast error. — Furqan Hameedi

rhoadsce rhoadsce · Accepted Answer · 2012-07-02T14:29:13

Definitely going to need more info from you before answering. Can you post some of your code (or a small reproduction of your issue)? Perhaps your table definition as well?

Since all of the values are numeric, why leave the column as an nvarchar?

Finally, does any of your data contain a dollar sign ($)?

This works:

DECLARE @Text nvarchar(100)

SET @Text = '1234.567'

SELECT CONVERT(float, @Text) AS ColumnValue

So does this:

DECLARE @Text nvarchar(100)

SET @Text = '    1234.567    '

SELECT CONVERT(float, @Text) AS ColumnValue

But this does not:

DECLARE @Text nvarchar(100)

SET @Text = '$1234.567'

SELECT CONVERT(float, @Text) AS ColumnValue

Cannot cast nvarchar to float

2 Answers