Calculate value from field ssql

Question

I have a column (varchar) in Postgres and the values look like this

1*2
9/3
23+33
2-1
3*3
123*32

I want to calculate the result straight from each cell. One way is to separate the values and then make the calculation. I want to ask if there is any way to do the calculation straight.

You can refer to this question: http://stackoverflow.com/questions/7433201/are-there-any-way-to-execute-a-query-inside-the-string-value-like-eval-in-post — MurifoX, Mar 05 '14 at 19:58

score 0 · Accepted Answer · answered Mar 05 '14 at 21:14

You can do it with a plpgsql function:

CREATE OR REPLACE FUNCTION evaluate_from_text(my_text character varying )
  RETURNS  text AS
$BODY$
declare 
s text;
x text;
begin

s:='';

FOREACH x  IN ARRAY string_to_array(my_text,' ') LOOP
  execute 'SELECT ' || x into x;    
    s := format ('%s %s',s , x);
  END LOOP;

return s;

end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

And output of:

Select evaluate_from_text('1*2 9/3 23+33 2-1 3*3 123*32');

is:

2 3 56 1 9 3936

Calculate value from field ssql

1 Answers1