Interpreting coded field in SQL

Question

Having this table, I would like to find the rows with Val fitting my Indata.

Tol field is a tolerance (varchar), that can be either an integer/float or a percentage value.

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 match rows 3 (24+10=34) and 4 (32-10%=28.8).

Can this be done in mySQL? CREATE FUNCTION?

Answer 1

Sure it can be done. But let me tell you that you better review your database design as it conflicts with normalization quite a bit. See http://en.wikipedia.org/wiki/Database_normalization for a quick overview.

Answer 2

This is going to be rather difficult to do in MySQL with that table and column design. How do you plan to differentiate what sort of comparison should be done? By doing a string comparison to see if your varchar field contains a percentage sign?

I would suggest breaking your tolerance field into (at least) two int/float columns, say tol and tol_pct. For flexibility, I would represent tol_pct as a decimal (10% => .10). Then, you can do a query that looks like:

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

I don't have a MySQL install to test it on, but this example is converted from Oracle sql syntax. You have to use string functions to determine if the tol is a percent and act accordingly to calculate the min and max range for that field. Then you can use a between clause.

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
;