In Postgres any line of the result 0 or '0'?

In the list of numeric values sometimes come across strings, you need to interpret them as 0.
How to do level Postgres'huh? Type function CAST translate from string to number only the rows corresponding to numbers and for example SELECT cast('asd' as INT) will give an error.
July 2nd 19 at 17:08
2 answers
July 2nd 19 at 17:10
It's a crutch, but will work
SELECT CASE WHEN some_field~E'^\\d+$' THEN some_field::integer ELSE 0 END FROM some_table;

If the rights in the database enough, you can define a rule with a type cast:
CREATE OR REPLACE FUNCTION cast_to_int(text) RETURNS integer AS $$
BEGIN
 RETURN CAST($1::varchar AS integer);
EXCEPTION
 WHEN invalid_text_representation THEN
 RETURN 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE CAST (text as integer) WITH FUNCTION cast_to_int(text);

SELECT CAST('asd'::text as integer) will return 0
July 2nd 19 at 17:12
it is possible and so
select regexp_replace('111a', '^(.*)[^\d].*$', '0')
-- 111 = 111
-- a111 = 0
-- 1a11 = 0
-- 111a = 0
-- sdfыва11ыва1a = 0

Find more questions by tags PostgreSQL