1. Registered User
Join Date
Mar 2016
Posts
3

Hi to everyone

i have a question about calculating with cells values

have a simple table

id (int), amount (int), share(text), value(int)

for ex:
1, 1000, "1/1", ????
2, 2000, "1/20" , ????
3, 100, "1/2", ????

what i wish is to calculate missing value
1000 * (1/1) = 1000
2000 * (1/20) = 100
100 * (1/2) = 50

etc

table format is fixed,i could not change anything, format,type etc... only what i could do is to update "VALUE" field for some other calculation

ty

## "SELECT id, amount, share, (amount * CAST(SUBSTRING(share, 1, INSTR(share, "/")-1) AS UNSIGNED INTEGER))/ CAST(SUBSTRING(share FROM INSTR(share, "/")+1 FOR (LENGTH(share)-INSTR(share, "/"))) AS UNSIGNED INTEGER) as value FROM MyTable; This will work for any numerator & denominator. You can try with weird shares like "5432/191" and "17/2341". I suggest you create a VIEW and never have to deal with that kind of crap ever after!!!"

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so lets get this straight....
because some (*&^&*^G has chose to represent a number as a text value it makes it tricky to use the numeric value. the design cannot be changed, but it sure looks real purdy.....

there are possible workarounds this frightmare
first and foremost... do the job properly, ignore the leave it alone diktat and save the 'share' column as numeric.
alternatively, and this depends on the data is parse the text
..so if ALL your shares are 1 in x, and expressed as 1/x
eg:-
Code:
```SELECT id, amount, share,  amount * ( 1 / substring(share, 3, length(share)-2)) as proportion
from mytable```
So the next phase could be to handle the leading term, instead of always presuming its 1.
Code:
```select id,amount,share,  amount*(substring(share,1,instr(share,"/")-1)/substring(share,instr(share,"/")+1,length(share)-instr(share,"/"))) as proportion
from mytable```
this is not the most efficient way of doing thigns, but then again whoever designed this didnt' relly give a fig about efficiency in the first place so maybe its good enough

...but frankly its starting to get unmanageable
the above code will fail if say there is no value, or no /

4. Registered User
Join Date
Mar 2016
Posts
3

## ok, will try this

Originally Posted by healdem
so lets get this straight....

this is not the most efficient way of doing thigns, but then again whoever designed this didnt' relly give a fig about efficiency in the first place so maybe its good enough
...but frankly its starting to get unmanageable
the above code will fail if say there is no value, or no /
Thank you, i will test it tomorrow and post result

and, yes, you are right. First number is not always 1, it could be 5000/20

it is table that represent share ownership of plow land generated by some old government program, and that is it, no change, no modify

thank you again

5. Registered User
Join Date
Sep 2002
Location
Posts
228
SELECT
id, amount, share, (amount *
CAST(SUBSTRING(share, 1, INSTR(share, "/")-1) AS UNSIGNED INTEGER))/
CAST(SUBSTRING(share FROM INSTR(share, "/")+1 FOR (LENGTH(share)-INSTR(share, "/"))) AS UNSIGNED INTEGER) as value
FROM MyTable;

This will work for any numerator & denominator. You can try with weird shares like "5432/191" and "17/2341".

I suggest you create a VIEW and never have to deal with that kind of crap ever after!!!

6. Registered User
Join Date
Mar 2016
Posts
3

## WOOOOW it's work

Thank you healdem , bstjean

Both of solutions work !!!

and Bstjean query finished in 0.8 sec on 100K rows

anyway, it is fantastic

thank you again