fLen fLen - 7 months ago 15
SQL Question

PostgreSQL: How to replace different value in the same field?

I have here a super uncleaned data (i must admit it -_-)

Field: Amount which is char

Values are '20 35' & '25..56'


How can i sum this with these two different value?

I can't used

cast(replace("Amount",' ','.') as DECIMAL)


since it only applied in Amount with space.

Already correct the datatype of its field.

Please help me. TIA! :D

Answer

You can use a regular expression to replace non-digits with a decimal point:

select cast(regexp_replace('25sd  ss343', '[^\d]+', '.', 'g') as decimal)