How can I dynamically convert integer 16ths of inch to fractional inch using mysql code? -

i have stored values size integers represent 16ths of inch.

example:

1 = 1/16" 4 = 1/4"  8 = 1/2" 16 = 1" 24 = 1 1/2" 32 = 2" 

i use 2 column table lookup of integer value , return calculated fraction of inch. prefer make more dynamic , have mysql select code or mysql function conversion on fly , not need store statically converted values every possible integer value.

this solution appears close reverse of need deals decimals vs integers not exact reverse.

the goal integer value input string output being division correct representation of fractional inch (i.e. 12 should 3/4", not 12/16" , 28 should 1 3/4", not 28/16")

the converted value isn't going used in where clauses or indexed not searchable value. output of results only.

expert ideas on how tackle math , conversion or there existing solution somewhere?

update solution implemented uses selected answer below:

concat(   if(sixteenths > 15,       /* integer part, if */    floor(sixteenths/16),    ''   ),   if(sixteenths % 16 = 0,   /* fractional part, if */    '',   concat(     if(sixteenths > 15, ' ', ''),  /* space between 1 , 1/2" */     substring_index(      substring_index(              /* use correct fractions set */      '1/16,1/8,3/16,1/4,5/16,3/8,7/16,1/2,9/16,5/8,11/16,3/4,13/16,7/8,15/16',      ',',       sixteenths % 16      ),    ',', -1   ))   ),   '"'            /* inches symbol */ ); 

i've used code in function can called when querying data many tables have large data sets in database need have conversion output when doing large results set (not used search criteria).

the resulting benefits dynamic, reusable, no limits pre-created data sets (i.e. can used integer of value not contemplated) , no tables required solution executed @ database level instead of application level fast when dealing large data results sets!

thanks!

the simplest way use substring_index reimplement lookup table. unfortunately, since function returns items 1 specified onwards, one element need invoke in complicated way:

first 1 call substring_index portion of string contain first n values, , second call last value subset!

in example extract 4th value string containing animals:

mysql> set @values = 'cat#dog#horse#parrot#gecko'; query ok, 0 rows affected (0.00 sec)  mysql> select substring_index(substring_index(@values,'#',4),'#',-1); +--------------------------------------------------------+ | substring_index(substring_index(@values,'#',4),'#',-1) | +--------------------------------------------------------+ | parrot                                                 | +--------------------------------------------------------+ 

in general, select n-th value string values contains values separated delimiter delim, have use:

substring_index( substring_index( values, delim, n ), delim, -1 ) 

this not more or less efficient mathematical formula, because, instance, both prevent optimization on sixteenths column if used in where. is, if search parts of '1 3/4"' caliber, server in both cases perform full table scan. such searches best handled back-translating queried value sixteenths before plugging query.

or can pour literal lookup table (i see considered this):

create table trans16(six integer, inch varchar(16));  select (select inch trans16 6 = sixteenths) textual, ... 

the first option work best last-step selects, while second more efficient larger, complex queries (the "back-translation" handled mysql).

alternately, can first extract "integer" part using if():

if(sixteenths >= 16, floor(sixteenths/16), '') 

this nothing values below 1 inch, or integer inches. then, value

`sixteenths % 16` 

will either 0 or number 1 15, so

if(sixteenths % 16 = 0,     '',    concat(' ', {formula_above_using_substring_index}  ) ) 

will yield either empty string or appropriate fractional part. can save concat placing space, fifteen times, in strings in set string.

the final formula looks like:

concat(     if(sixteenths > 15,       /* integer part, if */        floor(sixteenths/16),        ''     ),     if(sixteenths % 16 = 0,   /* fractional part, if */        '',        concat(          if(sixteenths > 15, ' ', ''),  /* space between 1 , 1/2" */          substring_index(              substring_index(                '1/16,1/8,3/16,1/4,5/16,3/8,7/16,1/2,9/16,5/8,11/16,3/4,13/16,7/8,15/16',                ',',                sixteenths%16              ),              ',', -1          )        )     ),     '"' ); 

you not able 3/2" way - represented 1 1/2".

the same logic can used user-defined function. integer part, lookup table sixteenths.

you might use approach lookup table also, isn't worth trouble.

finally, can without lookup table:

                  %4   %2 1       1/16      1    0 2       1/8       2    1 3       3/16      3    0 4       1/4       0    1 5       5/16      1 6       3/8       2 7       7/16      3 8       1/2       0 9       9/16 10      5/8 11      11/16 12      3/4 13      13/16 14      7/8 15      15/16 

so can have /2's, /4's, /8's, , /16'. when have them? when modulo 8 0 have halves; when modulo 4 0 have fourths; when modulo 2 0 have eighths; otherwise it's sixteenths.

the lookup table becomes

if((s%16)%8,     if((s%16)%4,         if((s%16)%2,             concat(s%16, '/16"')         ,concat(floor((s%16)/2), '/8"'))     ,concat(floor((s%16)/4), '/4"')) ,concat(floor((s%16)/8), '/2"')) 

and final formula without indexes, using table test:

select sixteenths, concat(             if(sixteenths > 15,         floor(sixteenths/16),         ''      ),      if(sixteenths % 16 = 0,         '',         concat(           if(sixteenths > 15, ' ', ''),             if((sixteenths%16)%8,               if((sixteenths%16)%4,                  if((sixteenths%16)%2,                     concat(floor(sixteenths%16), '/16')                       ,concat(floor((sixteenths%16)/2), '/8'))                   ,concat(floor((sixteenths%16)/4), '/4'))               ,concat(floor((sixteenths%16)/8), '/2'))         )      ),      '"'  ) `text` ttt; 

yields:

+------------+----------+ | sixteenths | text     | +------------+----------+ |          1 | 1/16"    | |          2 | 1/8"     | |          3 | 3/16"    | |          4 | 1/4"     | |          5 | 5/16"    | |          6 | 3/8"     | |          7 | 7/16"    | |          8 | 1/2"     | |          9 | 9/16"    | |         10 | 5/8"     | |         11 | 11/16"   | |         12 | 3/4"     | |         13 | 13/16"   | |         14 | 7/8"     | |         15 | 15/16"   | |         16 | 1"       | |         17 | 1 1/16"  | |         18 | 1 1/8"   | |         19 | 1 3/16"  | |         20 | 1 1/4"   | |         21 | 1 5/16"  | |         22 | 1 3/8"   | |         23 | 1 7/16"  | |         24 | 1 1/2"   | |         25 | 1 9/16"  | |         26 | 1 5/8"   | |         27 | 1 11/16" | |         28 | 1 3/4"   | |         29 | 1 13/16" | |         30 | 1 7/8"   | |         31 | 1 15/16" | |         32 | 2"       |