Converting from varchar to numeric

Question

I have two columns latitude and longitude. I should have been set them to numeric upon importing from the fixed width file, but do not have the time to re-do that process. I'm using SQL Server Express and have to use the import wizard. There are 92 columns.

Anyways, using the following code and getting an error (shown below) when I try to change it from varchar(9) to numeric (11,6). I have modified the settings to where I can make changes to names and datatypes. I know that using the design feature in object explorer will yield a similar error. Any other ways around this dilemma?

Code:

alter table dbo.tablename
    alter column latitude numeric(11,6);  

Error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Just found the following code:

alter table tablename
alter column latitude float

Why did this work, but not the previous?

Answer 1

You need to find a value. I'm not sure is SQL Server Express supports isnumeric(), so this is one possibility:

select latitude
from tablename
where isnumeric(latitude) = 0;

Otherwise, you can approximate this with like:

where latitude not like '%[^0-9.]%' and
      latitude not like '%.%.%'