MSSQL Convert String into Number and do Math


I have a string ((223533/1024)/1024)*8 which I want to convert into a number and do the math in the process.

Many thanks

Si Brasso
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

just one string?
from a web form?
what happens to the calculated result?

can you give us more overall 'context' to this question?
brasso_42Author Commented:
in a sql query e.g Select ((223533/1024)/1024)*8

would return 1.7

but the "((223533/1024)/1024)*8" comes from a field which has a data type of varchar(150)

So I need to some how convert it to a number but doing the math.

Hope this helps explain
not for me I'm afraid

a) do you have a table of 1,000,000 of these calculations to perform, or
b) do you accept one string from a web form, or
c) something else (please explain)

What will you do with the result?
print it
store it

Do you also need to store the originating string(s)?

and: why does this have to be performed within sql server?
(e.g. if it's option b) above - then why do it in the dbms at all?

Please remember that all we know about this is what is on this page.

if it's less complex than I'm suggesting, then one approach would be "dynamic sql"

e.g. create a stored procedure that accepts that string as a parameter
then in that procedure concatenate

@sql ="select @var= " + that-string

exec @sql

select @var

You open a real can of worms for "sql injection" with such an approach
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

brasso_42Author Commented:

There could be 100's but no more than a few thousand.

I'm writing something to monitor SNMP and the results are always in different measurements e.g. Mbps, Kbps, MBps, etc.  and that's just looking at network speed.  I have a table that has the OID to query the device and I've got the results in a table but before displaying the results I'd like to have a uniform measurement.  I though I'd do this by putting a math column in my table containing the OID e.g.  ((Result/1024)/1024)*8 which would convert Bps into Mbps.  I've used a replace(Math,'Result',c.Restult)  to get my string.  Now Dynamic SQL does work but I'd like to create a function to do this and that rules out dynamic SQL.

Sorry for the lack of explanation to start with I though it would be easy, silly me!

Can you think of a better way to do this?

many thanks

Si Brasso
Scott PletcherSenior DBACommented:
For any type of complex computation, you'll need dynamic SQL, specifically sp_executesql (not EXEC).

If all the computations are as simple as above -- multiply and/or divide only using constants and one or more variable values -- then you could use a trigger to pre-compute the constant value(s) and substitute the varying values at run time.  That would not be trivial, but it would be way better than trying to write something yourself to dynamically evaluate a complex expression.
Scott PletcherSenior DBACommented:
For just a few thousand rows, I would just use a cursor and pass the formulas thru sp_executesql.  That also means you could include SQL functions, such as SQRT, CEILING, etc., or even your own UDFs.
my bad: "sp_executesql" it is

you could most probably also do " replace(Math,'Result',c.Restult)  " within the loop that produces the dynamic sql.

Thanks for the additional details, it makes sense now :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brasso_42Author Commented:
awesome thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.