How to delete rows which can't be cast to INT

Question

I have this sample table with a housenumber in my postgresql 9.1:

drop table if exists mytable;
create table mytable(road_id int, housenr text);
insert into mytable(road_id, housenr) values

('11', '1' ),
('22', '12' ), 
('33', '99/1' ),
('44', '88' ),
('55', '2' ),
('66', '28' ),
('77', '29')
;

Now I have to convert the whole column "housenr" into an INT field. Is there a way in SQL to cast only these rows from the column which can be casted. In mytable this would be every row except the one with "housenr" = 99/1.
Something like: FOR EACH ROW IF ::int IS POSSIBLE cast the row ELSE REMOVE FROM TABLE

Answer 1

You can use REGEX to evaluate your column values to determine if it is numeric:

select * FROM MyTable where (housenr !~ '^[0-9]+$')

Here is the SQLFiddle:

http://sqlfiddle.com/#!1/d2ff3/9

Here is the Postgresql documentation on ~ and ~!

http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-TABLE