Check the result of a math expression in mysql

Question

Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.

a  b  oper  c
-------------
2  3  +     5
4  2  /     3
6  1  *     9
8  5  -     3

As, we can see in some cases, a <oper> b != c. So, my question is how to filter out such cases?

I've heard of execute, which is used for executing statements, but I don't know how to use it inside where clause.

Also, I'm not generalizing the oper to any arithmetic operation, but it would be nice to know, if any function exists.

score 3 · Accepted Answer · answered Nov 24 '20 at 04:26

Because SQL differentiates code and data reasonably well in the syntax function like the following is required:

CREATE OR REPLACE FUNCTION test(a int, op char(1), b int)
   RETURNS INT DETERMINISTIC RETURN
CASE op
WHEN '+' THEN a + b
WHEN '*' THEN a * b
WHEN '/' THEN a / b
WHEN '-' THEN a - b
END

once you have that you can use a query:

select a, op, b, c
from maths
where test(a,op,b) = c

ref: fiddle

Check the result of a math expression in mysql

1 Answers1