March 30, 2022 at 7:10 am
Hi,
I am trying to convert several columns from varchar to float. I have managed to do it for most of them but a few of them error out with this message "Error converting data type varchar to float."
As it turns out, some of the cells in the uploaded source file have #Div/0 error which is why the conversion is failing. What I want to do is replace all values where it errors with nulls. I have attached a sample table here with my query
IF OBJECT_ID( 'TempDB..#Data', 'U' ) IS NOT NULL
DROP TABLE #Data;
CREATE TABLE #Data (
[Number] Varchar (10)
);
INSERT INTO #Data ( [Number] )
VALUES ( '123.6' )
, ( '123.7' )
, ( '123.7' )
-------
, ( '213' )
, ( '213.7' )
, ( '345.8' )
, ( 'a' )
-------
, ( 'B' )
, ( 'C' )
ALTER TABLE #Data ALTER COLUMN [NUMBER] FLOAT
What I am trying to achieve is to convert all varchar to float and where it errors out, to NULL ( in this case 'a','b' ,'c')
Thanks in advance!
March 30, 2022 at 9:42 am
First of all, are you aware of issues with numeric precision when using FLOAT? If not, I suggest you reconsider – DECIMAL() is often a better choice.
If you put the following code just before your ALTER statement, all should be well.
UPDATE #Data
SET Number = NULL
WHERE TRY_CAST(Number AS FLOAT) IS NULL;
March 30, 2022 at 9:47 am
You can use TRY_CAST or TRY_CONVERT
UPDATE #Data
SET Number = TRY_CAST(Number AS float);
March 30, 2022 at 10:38 am
thank you! that did the trick.
March 30, 2022 at 4:15 pm
What reason are you using a float?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 30, 2022 at 9:12 pm
When I try to run this query on my data (not the sample posted here) I get an invalid object error "Column name" even though the column exists in the model
Some of the values in the raw dataset are #DIV/0! or negative. Could that be causing the issue ?
EDIT---
Please ignore this. Manged to fix it. As with most SQL issues the cause was amateur SQL user ignorance
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy