Interpreting a coded field in SQL

Question

Having this table, I would like to find lines with a Val parameter setting my Indata.

The Tol field is a varchar, which can be either an integer, a floating, or a percentage.

Row Val Tol   Outdata
1   24  0     A
2   24  5     B
3   24  10    C
4   32  %10   D
5   32  1     E

Indata 30, for example, should correspond to lines 3 (24 + 10 = 34) and 4 (32-10% = 28.8).

Can this be done in mySQL? CREATE FUNCTION?

Answer 1

MySQL , SQL sql. , , tol min max . inter.

select * 
  from (select t.*,
               case when substr(tol, 1, 1) = '%' then 
                      t.val * (1 + convert('.' + substr(tol, 2), number))
                    else 
                      t.val + convert(tol, number)
               end maxval,
               case when substr(tol, 1, 1) = '%' then 
                      t.val * (1 - convert('.' + substr(tol, 2), number))
                    else convert(t.val - tol, number)
               end minval
          from mytable
       ) t
where 30 between minval and maxval
;

Answer 2

MySQL . , ? , , varchar ?

( ) int/float, tol tol_pct. tol_pct (10% = > .10). , :

select * 
from table 
where
    (Indata between Val - tol and Val + tol) 
    or (Indata between Val * (1 + tol_pct) and Val * (1 - tol_pct))

Answer 3

Of course, this can be done. But let me tell you that you’d better analyze the design of your database, as this is a bit contrary to normalization. For a quick overview, see http://en.wikipedia.org/wiki/Database_normalization .