retrieving a string into mathematical expression

[clear1140]

Good Day!!!

Is it somehow possible to retrieve a string and then convert it into a mathematical expression? for example i have this string '14200 * 5' and then i want to convert this into a mathematical formula so that i could get the result of it which is 71000

Thanks....

[Jim Doherty]

Good Day!!!

Is it somehow possible to retrieve a string and then convert it into a mathematical expression? for example i have this string '14200 * 5' and then i want to convert this into a mathematical formula so that i could get the result of it which is 71000

Thanks....

The Function EVAL is what you need to look at ie:

ie: myvalue=Eval('14200*5')

assigns the value 71000 to the variable myvalue in vba code

you can also use the Eval function in SQL

ie: SELECT Eval('14200*5') AS Blah;

will return you a single column called blah in which contains a single row value of 71000

There are obviously any number of ways you may wish to use the return value either in SQL or VBA code

Regards

Jim

[clear1140]

i tried to use the eval function but it seems that MSSQL does not recognize the function

[Jim Doherty]

i tried to use the eval function but it seems that MSSQL does not recognize the function

Apologies I posted this reply thinking it was the Access forum didnt realise it was the MS SQL forum ..there you are we all make stupid mistakes LOL I flick back and forth between them like tennis :)

Jim :)

[clear1140]

Apologies I posted this reply thinking it was the Access forum didnt realise it was the MS SQL forum ..there you are we all make stupid mistakes LOL I flick back and forth between them like tennis :)

Jim :)

Apologies accepted.... but still i dont know what function to use... :(

[Jim Doherty]

Apologies accepted.... but still i dont know what function to use... :(

There isnt an EVAL function in Sql server like there is in Access it might well be a wish list thing. In the absence of it you could maybe workaround with something like this stored procedure accepting a string parameter and executing a simple select to return a single row with the data value being the result of your parameter passed in?

 
CREATE PROCEDURE dbo_usp_Eval
(@mystr varchar(4000))
AS
SET NOCOUNT ON
DECLARE @SQLString NVARCHAR(4000)
SET @SQLString = 'SELECT '+@mystr
EXEC sp_executesql @SQLString
GO
 

a typical execution of this might be this from query analyser or an application

EXEC dbo_usp_Eval '14200 * 5'

it depends largely on how you are dealing with your data and what you are actually trying to do. If you are thinking of a user defined function to use in a view as part of a SELECT as a column then I think you're out of luck? unless somelse comes up with something

Regards

Jim :) :(

[Vanaspati Bhavsar]

Hi

Instead of using select Eval('14000*5')

use

exec('select 14200*5') in sql

I think this should work

All the best

[Daeku]

Disclaimer: I'm the owner of the project Eval SQL.NET on GitHub

Starting from SQL Server 2012, it's possible to evaluate arithmetic/mathematical expression in T-SQL using this SQL CLR.

-- Without parameter
DECLARE @result1 INT = SQLNET::New('14200*5').EvalInt()
-- With parameter
DECLARE @result2 INT = SQLNET::New('X*Y').Val('X', 14200).Val('Y', 5).EvalInt()
 
-- SELECT 71000, 71000
SELECT @result1, @result2

Documentation: Dynamically evaluate mathematical expression