Convert varchar into math calculation

database-symbolI try to google, call, msn my friend to find out the solution when I stay back in my office until late night yesterday. But still no luck after few hours of trying.

Thinking of gave up when back to office this morning, but one of my friend suddently send me the solution via MSN. That’s the best present I received by today. I’m not sure how many people will looking for this kind of solution, but any way, will post here to share with all and you may take it if you need it.

Basically the idea is that you put all your select, update or delete statment into the variable and use the execute command to execute the whole variable.

My problem:

I have a formula store in a table which is A+B*C+D. My A = 1, B=2, C=3, D=4.

I managed to replace all my variable with the integer using cursor (This maybe not a good solution, but this is not the main discussion in this post)

The finally answer I get is 1+2*3+4. But the bad thing is in the varchar datatype. I only manage to disply 1+2*3+4 but not the answer of 11.

The solution as below:

Declare @MathFomula Varchar(100)

Set @MathFormula = ‘1+2*3+4’

Exec(‘SELECT ‘ + @MathFormula  + ‘ AS Result’

Output: 11

* Remember that * and / will calculate at first before + and –


Leave a Reply, pub-3772983857049267, DIRECT, f08c47fec0942fa0
%d bloggers like this: