CODEWITHSUNDEEP
sqlpostgresql
fLen
fLen

Reputation: 598

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

Upvotes: 1

Views: 61

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

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

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

Upvotes: 3

Related Questions