postgresql - How to turn String to Numeric With Characters in it (Postgres) -

i have field string want turn numerics. problem field can contain characters , it's hard me simple regular expression replace. here example,

field1 1 1.5 2adfk3 2. section 2 0'#%0ls23 3.987

what want if field can natively casted numeric, return numeric, otherwise return null. example, want field, be

field2 1 1.5 null null null 3.987

where field2 numeric. best way this?

in excel suppose closest iferror

you can case expression , regex:

select column1,        case             when column1 ~ '^[0-9\.]+$' column1::numeric            else null        end column2 the_table

the regex checks if contents of column consists of numbers , dot. if case, value can cast numeric value, otherwise returns null

First Image