How to convert any string to 0 or '0' in Postgres?

Question

PostgreSQL

Sometimes there are strings in the list of numeric values, you need to interpret them as 0.
How to do this at the Postgres level? Functions like CAST translate from string to number only strings corresponding to numbers, and for example SELECT cast('asd' as INT) will give an error.

Answer 1

It's a crutch, but it will work
If there are enough rights in the database, then you can define your own type casting rule:

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

Answer 2

you can also
select regexp_replace('111a', '^(.*)[^\d].*$', '0')
-- 111 = 111
-- a111 = 0
-- 1a11 = 0
-- 111a = 0
-- sdfvy11vy1a = 0