Error unable to convert data type nvarchar to float

Question

I have searched both this great forum and googled around but unable to resolve this.

We have two tables (and trust me I have nothing to do with these tables). Both tables have a column called eventId.

However, in one table, data type for eventId is float and in the other table, it is nvarchar.

We are selecting from table1 where eventI is defined as float and saving that Id into table2 where eventId is defined as nvarchar(50).

As a result of descrepancy in data types, we are getting error converting datatype nvarchar to float.

Without fooling around with the database, I would like to cast the eventId to get rid of this error.

Any ideas what I am doing wrong with the code below?

SELECT 
    CAST(CAST(a.event_id AS NVARCHAR(50)) AS FLOAT) event_id_vre,

Answer 1

The problem is most likely because some of the rows have event_id that is empty. There are two ways to go about solving this:

• Convert your float to nvarchar, rather than the other way around - This conversion will always succeed. The only problem here is if the textual representations differ - say, the table with float-as-nvarchar uses fewer decimal digits, or
• Add a condition to check for empty IDs before the conversion - This may not work if some of the event IDs are non-empty strings, but they are not float-convertible either (e.g. there's a word in the field instead of a number).

The second solution would look like this:

SELECT 
     case when a.eventid <> '' 
            then cast(cast(a.event_id as nvarchar(50)) as float) 
          ELSE 0.0 
     END AS event_id_vre,