Remove Invalid data from VarChar column

Question

I have a database with a column of VarChar type, within which are integers (which I want to keep) and miscellaneous non-numeric values (which I want to remove). If I alter the table and add a new integer column, how can I copy only the integers into the new integer column?

Answer 1

I'd give this a shot (will work with MSSQL, not sure about other database systems)...

update MyTable
set    MyNewIntField = cast(MyOldVarcharField as int)
where  isnumeric(MyOldVarcharField) = 1

Answer 2

this should do it for you if you are running MS SQL:

update mytable set new_column = old_column where isnumeric(old_column) = 1

Answer 3

You will fail out if you end up trying to put decimal data into the integer column.

You have 2 choices here

you could only accept data if it is whole numbers:

 update [table]
 set [integer column] = [varchar column]
 where isnumeric([varchar column])=1 and ([varchar column] not like '%.%')

or you could round or truncate the decimals

--truncate
 update [table]
 set [integer column] = floor(cast([varchar column] as float))
 where isnumeric([varchar column])=1
 --round
 update [table]
 set [integer column] = round(cast([varchar column] as float),0)
 where isnumeric([varchar column])=1