CODEWITHSUNDEEP
sqlpostgresqlcasting
zehpunktbarron
zehpunktbarron

Reputation: 1223

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

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

Upvotes: 10

Views: 4426

Answers (2)

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

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

Upvotes: 14

bonCodigo
bonCodigo

Reputation: 14361

You may do a case when

select * from (
    select roadid, case when 
    instr(housenr,"/") > 0 then 0 else cast(housenr as int) end) as housenr_new
    from mytable) t
where t.housenr_new <> 0
    ;

Using a regex with case when to return a 0 when the field is not int

SELECT roadid, CASE WHEN housenr ~ E'^\\d+$' 
THEN housenr::integer ELSE 0 END as housenr_new
FROM mytable;

Upvotes: 0

Related Questions