Error Unable to Convert Data Type Nvarchar to Float
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)...
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,
4 Answers
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
floattonvarchar, rather than the other way around - This conversion will always succeed. The only problem here is if the textual representations differ - say, the table withfloat-as-nvarcharuses 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,
I also received the message "Error converting data type nvarchar to float". This was from a query in SQL Server Management Studio (SSMS) from SQL Server 2019.
In my case there were undecipherable characters in the source data, which was exported to CSV from Excel. Some columns were in the "Accounting" format, which has spaces, currency symbols, commas, and parenthesis, like this:
- positive values: $ 123,456.78
- negative values: $(123,456.78)
To account for this and resolve the error, I simply inserted a nested REPLACE() function to remove some characters and replace others, and wrapped it with IsNull() to return zero when necessary.
First I will describe:
- replace any
$,)with nothing (aka null string or empty quotes'') - replace any
(with-(aka hyphen or negative symbol)
And here is the SQL:
Original:
CONVERT(float, exp_TotalAmount)
Replacement:
CONVERT(float, IsNull(REPLACE(REPLACE(REPLACE(REPLACE(exp_TotalAmount,'(','-'),')',''),',',''),'$',''),0))
Here broken up for easier read, innermost first:
REPLACE(exp_TotalAmount, '(', '-')
REPLACE(X, ')', '')
REPLACE(X, ',', '')
REPLACE(X, '$', '')
IsNull (X, 0)
CONVERT(float, X)
I've had this issue as well when trying to insert into an existing sql table. Mine wasn't specific to a column. What I did was to specify the column I was inserting and I did it in the order they sat in the table I was inserting into. Its not a practical solution and might not work if you have too many columns. something like this:
INSERT INTO [DBName].[dbo].[TableName1] SELECT list your columns in [TableName1] order here FROM [DBName].[dbo].[TableName1]
Convert float to nvarchar instead of nvarchar to float. Of course!
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
