Evaluate mathematical expressions

mysql, sql

Solution

Since you can't use `EXECUTE IMMEDIATE` or prepared statements in `CREATE FUNCTION` (which would have helped in evaluating the expressions with a simple `CONCAT("SELECT ", expr, " FROM dual")`), please see below a solution that makes use of a function that actually computes the mathematical expression (since your question concerns only simple expressions with `+` and `-` operators)

DELIMITER $$

CREATE FUNCTION calc(expr VARCHAR(255)) RETURNS FLOAT
BEGIN
  DECLARE result FLOAT;
  DECLARE operand VARCHAR(255);
  DECLARE operator INT;
  DECLARE i INT;
  DECLARE c CHAR;

  SET i = 1;
  SET result = 0;
  SET operand = 0;
  SET operator = 1;

  WHILE(i <= LENGTH(expr)) DO
    SET c = SUBSTR(expr, i, 1);

    IF c = '+' THEN
        SET result = result + operator * operand;
        SET operator = 1;
        SET operand = '';
    ELSEIF c = '-' THEN
        SET result = result + operator * operand;
        SET operator = -1;
        SET operand = '';
    ELSE
        SET operand = CONCAT(operand, c);
    END IF;

    SET i = i + 1;
  END WHILE;

  SET result = result + operator * operand;

  RETURN result;
END$$

After you create this function you can simply use the `SELECT` command:

SELECT calc(col) FROM tbl;

Here's the DEMO.

Problem

I have column which type is `varchar`, values of this column are mathematical operations (just addition and minus operations) ``` col ------ 2+3+2+1 3+3-4 1+1-2.5 ``` There is possible, that evalute this expressions? that is needed result is: ``` col ---------- 8 2 -0.5 ```

Original source