Selecting a fraction stored as String as decimal value in Postgresql

Question

I have a String column in Database which stores values like: 5/4 , 3/9, 4/3 etc. I have a requirement to select this column value in a query and display it as a number/decimal.

On using ::float operator or to_number() function on the column, it gives an error: invalid input syntax for type double precision: "5/4"

I have to do it with a single select statement without use of any procedures or custom functions. Any Helps regarding this?

Answer 1

You would need to split that string, convert each part and divide:

select col, split_part(col, '/', 1)::numeric / split_part(col, '/', 2)::numeric as res
from mytable