Sql – Remove Invalid data from VarChar column


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?

Best Solution

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