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?
PortletPaulConnect With a Mentor Commented:
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 :)
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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
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.
brasso_42Author Commented:
awesome thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.