CODEWITHSUNDEEP
postgresql
Syrupsystem
Syrupsystem

Reputation: 181

Convert string fraction into float

We're trying to convert in PostgreSQL something like this:

select '1/2'::float;

ERROR:  invalid input syntax for type double precision: "1/2"
LINE 1: select '1/2'::float;

And actually this works:

select 1/2::float;
  ?column? 
----------
    0.5

Are we missing something? We have tried to_number... The only solution we came threw was to use split...

Upvotes: 2

Views: 1506

Answers (2)

Mark Schneider
Mark Schneider

Reputation: 380

Try this:

(Borrowing from https://xzilla.net/blog/2006/Dec/sql-servers-charindex-function-in-postgresql.html and Ross Pressor's answer at Convert fractional string to decimal for SQL Server)

create or replace function charindex(text, text, integer) returns integer as $$
begin
    return 
        case 
        when strpos(substr($2, $3 + 1), $1) = 0 
        then 0
        else strpos(substr($2, $3+1), $1) + $3 
        end;
end;
$$ language plpgsql;

create or replace function fraction_to_decimal ( frac varchar(100) ) returns decimal as $$
begin
    return case 
        when frac like '% %/%'
            then cast(left(frac, charindex(' ', frac, 1) -1) as decimal) + 
                ( cast(substring(frac, charindex(' ', frac, 1) + 1, charindex('/', frac, 1)-charindex(' ',frac,1)-1) as decimal) 
                / cast(right(frac, length(frac) - charindex('/', frac, 1)) as decimal) )
        when frac like '%/%'
            then cast(left(frac, charindex('/', frac, 1) - 1) as decimal) / cast(right(frac, length(frac) - charindex('/', frac, 1)) as decimal)
        else 
            cast(frac as decimal) 
        end;
end;
$$ language plpgsql;

Then: SELECT fraction_to_decimal('1/2');

Also works fine for nulls: SELECT fraction_to_decimal(null);

Upvotes: 0

user330315
user330315

Reputation:

Are we missing something?

Yes, you are.

1/2::float means "the value 1 divided by the value 2 cast as a float".

'1/2'::float means: cast the string '1/2' to a float value - but the / character is invalid for a number constant.

A valid number in SQL consists of the digit 0-9 a dot . for the decimal separator and optionally an e to indicate exponential values (e.g. 1e6 for "one million"). So '1/2'::float is wrong in the same way as '1(2'::float or '1 divided by 2'::float

And before you ask for a function to do that for you, see the following questions for a generic "eval()" function:

Upvotes: 1

Related Questions