Problem in changing datatype of columns with null values in sql server 2008

[Shweta]

Hello All,

I have imported some data from a text file into SQL server 20008 R2. All the columns of the data have been imported as varchar(50). Now I am trying to change the datatype of columns using alter table statement. But the problem is, there are few numeric columns with missing values (as I got this data from SAS, it shows dot(.) in numeric columns where the value is missing). When I try to convert them from varchar to float, SQL throws an error "Error converting data type varchar to float.". Kindly suggest.

Regards,
Shweta Jain

 

[Kyle123]

Can't you just run update statements to make them numeric before trying to change the data type?

 

[xenou]

Yes, you will have to clean up that data first. Perhaps update them to Null is another way to go.

 

[Shweta]

Ok. Thank you. Let me give it a try with UPDATE statement.

 

[Shweta]

I have used below query to update my columns. Please suggest if there is any other way to do it instead of updating columns one by one.

update Car_sales
set [4-year resale value]= NULL WHERE [4-year resale value] = '.'

update Car_sales
set [price in thousands] = null where [price in thousands]='.'

update Car_sales
set [engine size] = null where [engine size]='.'

update Car_sales
set horsepower = null where horsepower = '.'

update Car_sales
set wheelbase = null where wheelbase ='.'

update Car_sales
set width = null where width ='.'

update Car_sales
set [length]=null where [length]='.'

update Car_sales
set [curb weight]=null where [curb weight]='.'

update Car_sales
set [fuel capacity]=null where [fuel capacity]= '.'

update Car_sales
set [fuel efficiency]=null where [fuel efficiency]='.'

 

[xenou]

I don't see anything particularly bad with that, since it's a one time cleanup.
A more complex script (that you need to be careful with to make sure it's 100% correct) would be like this, all in one go:

update 

	Car_sales

set 

	[4-year resale value] = case when [4-year resale value] = '.' then null else [4-year resale value] end,
	[price in thousands]  = case when [price in thousands]  = '.' then null else [price in thousands]  end,
	[engine size]         = case when [engine size]         = '.' then null else [engine size]         end,
	horsepower            = case when horsepower            = '.' then null else horsepower            end,
	wheelbase             = case when wheelbase             = '.' then null else wheelbase             end,
	width                 = case when width                 = '.' then null else width                 end,
	[length]              = case when [length]              = '.' then null else [length]              end,
	[curb weight]         = case when [curb weight]         = '.' then null else [curb weight]         end,
	[fuel capacity]       = case when [fuel capacity]       = '.' then null else [fuel capacity]       end,
	[fuel efficiency]     = case when [fuel efficiency]     = '.' then null else [fuel efficiency]     end

where

(
	[4-year resale value]   = '.'
	or [price in thousands] = '.'  
	or [engine size]        = '.'
	or horsepower           = '.'   
	or wheelbase            = '.'
	or width                = '.'
	or [length]             = '.'
	or [curb weight]        = '.' 
	or [fuel capacity]      = '.'
	or [fuel efficiency]    = '.'
	
)