Datatype conversion error_ replace with NULL

Question

Datatype conversion error_ replace with NULL

masterelaichi

SSC-Addicted

Points: 444
More actions
March 30, 2022 at 7:10 am

#4008043
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!
- This topic was modified 2 years, 8 months ago by masterelaichi.
- This topic was modified 2 years, 8 months ago by masterelaichi.
April 1, 2022 at 7:11 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DesNorton SSC-Insane Points: 24387 More actions · Answer 2

You can use TRY_CAST or TRY_CONVERT

UPDATE #Data
SET Number = TRY_CAST(Number AS float);

How to post data/code on a forum to get the best help.

masterelaichi SSC-Addicted Points: 444 More actions · Answer 3

masterelaichi

SSC-Addicted

Points: 444

March 30, 2022 at 10:38 am

#4008144

thank you! that did the trick.

Michael L John One Orange Chip Points: 27055 More actions · Answer 4

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/

masterelaichi SSC-Addicted Points: 444 More actions · Answer 5

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 😀

This reply was modified 2 years, 8 months ago by masterelaichi.
This reply was modified 2 years, 8 months ago by masterelaichi.