Datatype conversion error_ replace with NULL
-
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] FLOATWhat 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!
-
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; -
You can use TRY_CAST or TRY_CONVERT
UPDATE #Data
SET Number = TRY_CAST(Number AS float); -
thank you! that did the trick.
-
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/ -
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