fLen fLen - 1 year ago 78
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 Source

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

select cast(regexp_replace('25sd  ss343', '[^\d]+', '.', 'g') as decimal)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download