CODEWITHSUNDEEP
sqlstringpostgresql
Sarthak Mehra
Sarthak Mehra

Reputation: 51

Selecting a fraction stored as String as decimal value in Postgresql

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?

Upvotes: 4

Views: 902

Answers (1)

GMB
GMB

Reputation: 222462

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

Upvotes: 5

Related Questions