CODEWITHSUNDEEP
sqlpostgresqlcasting
codeworks
codeworks

Reputation: 159

Convert a percentage(string, with a %) to a decimal in postgresql

I would like to average on the Scores(string) of each person from the following table in postgresql,

No. | Name    | Term   | Score
1   | A       | 1      | 95.00%
2   | A       | 2      | 99.00%
3   | C       | 1      | 90.00%
4   | D       | 1      | 100.00%
.
.

It does not like % on the score. How can I convert it into a decimal/float from a string containing a % as shown above?

Tried, score::decimal but it complains as,

ERROR:  invalid input syntax for type numeric: "95.00%"
SQL state: 22P02

cast also does not seem to work.

How do I convert this?

Upvotes: 1

Views: 4357

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method uses replace():

select replace(score, '%', '')::numeric

If you actually want to convert it to a number between 0 and 1 rather than 0 and 100, try a case:

select (case when right(score, 1) = '%'
             then (replace(score, '%', '')::numeric) / 100
             else score::numeric
        end)

Upvotes: 3

Related Questions