String Expression to be evaluated to number
Solution 1
I don't think that is possible in a user defined function.
You could do it in a stored procedure, like:
declare @calc varchar(max)
set @calc = '10*4.5*0.5'
declare @sql nvarchar(max)
declare @result float
set @sql = N'set @result = ' + @calc
exec sp_executesql @sql, N'@result float output', @result out
select @result
But dynamic SQL, like exec
or sp_executesql
, is not allowed in user defined functions.
Solution 2
Disclaimer: I'm the owner of the project Eval SQL.NET
For SQL 2012+, you can use Eval SQL.NET which can be run with SAFE Permission.
The performance is great (better than UDF) and honors operator precedence and parenthesis. In fact, almost all the C# language is supported.
You can also specify parameters to your formula.
-- SELECT 225.00
SELECT 10 * CAST(SQLNET::New('10*4.5*0.5').Eval() AS DECIMAL(18, 2))
-- SELECT 70
DECLARE @formula VARCHAR(50) = 'a+b*c'
SELECT 10 * SQLNET::New(@formula)
.Val('a', 1)
.Val('b', 2)
.Val('c', 3)
.EvalInt()
PraveenVenu
I am here to help you and to get some help on technology questions
Updated on June 11, 2022Comments
-
PraveenVenu about 2 years
I need to write a TSQL user defined function which will accept a string and return a number.
I will call the function like
dbo.EvaluateExpression('10*4.5*0.5')
should return the number22.5
Can any one help me to write this function
EvaluateExpression
.Currently I am using CLR function which I need to avoid.
Edit1
I know this can be done using stored procedure, but I want to call this function in some statements ex:
select 10* dbo.EvaluateExpression('10*4.5*0.5')
Also I have around 400,000 formulas like this to be evaluated.
Edit2
I know we can do it using osql.exe inside function as explained here. But due to permission settings, I can not use this also.