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

Question

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

FuzzyTree · Accepted Answer · 2016-05-06 05:34:01Z

3

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

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

answered May 6, 2016 at 4:57

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for this help, but it become whole number. from 25..56 it became 2556.. The result should be 25.56 :)