String Expression to be evaluated to number

Question

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 number 22.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.

Answer 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.

Answer 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()

Answer 3

You can use the SQL Stored procedure below to calculate the result of any formula with any number of variables:

I wrote in 2012 a solution which can evaluate any type of Mathematical formula using SQL SERVER. The solution can handle any formula with N variables :

I was asked to find a way to evaluate the value given by a Formula which is filled by the user. The Formula contains mathematical operations (addition, multiplication, division and subtractions) The parameters used to calculate the formula are stored in the SQL server DATA BASE.

The solution I found by myself was as follows:

Suppose I have n parameters used to calculate the formula, each of these parameters is stored in one row in one data table.

• The data table containing the n rows to use in the formula is called tab_value

• I have to store the n values found in n rows (in tab_values) in one single row in one new Table, using SQL cursor,

• for that I create a new table called tab_formula

• In the cursor, I will add a new column for each value, the column name will be Id1,Id2,Id3 etc.

• Then I construct a SQL script containing the formula to evaluate the formula

Here after the complete script, I hope you find it useful, you are welcome to ask me about it.

The procedure uses as input:

-The formula

-A table containing the values used to calculate the formula

if exists(select 1 from sysobjects where name='usp_evaluate_formula' and xtype='p') 

drop proc usp_evaluate_formula 

go

create type type_tab as table(id int identity(1,1),val decimal(10,2)) 

go 
create proc usp_evaluate_formula(@formula as nvarchar(100),@values as type_tab readonly) 

as begin 

declare @tab_values table (id int, val decimal(10,2))

insert into @tab_values(id,val) select * from @values

declare @id as int declare @val as decimal(10,2)
if not exists(select 1 from sysobjects where name ='tab_formula') 
create table tab_formula(id int identity(1,1), formula nvarchar(1000))

if not exists(select 1 from tab_formula where formula=@formula) 
insert into tab_formula(formula) values(@formula)


declare c cursor for select id,val from @tab_values 

declare @script as nvarchar(4000) 

open c 

fetch c into @id,@val 

while @@fetch_status=0 

begin 

set @script = 'if not exists(select 1 from syscolumns c inner join sysobjects o on c.id=o.id where o.name=''tab_formula'' and c.name=''id'+
convert(nvarchar(3),@id)+ ''') 
alter table tab_formula add id'+convert(nvarchar(3),@id)+ ' decimal(10,2)' 

print @script 

exec(@script) 

set @script='update tab_formula set id'+convert(nvarchar(3),@id)+'='+convert(nvarchar(10),@val)+' where formula='''+@formula+'''' print @script exec(@script) fetch c into @id,@val end close c deallocate c

set @script='select *,convert(decimal(10,2),'+@formula+') "Result" from tab_formula where formula='''+@formula+'''' 

print @script 

exec(@script)

end

go

declare @mytab as type_tab 

insert into @mytab(val) values(1.56),(1.5) ,(2.5) ,(32),(1.7) ,(3.3) ,(3.9)

exec usp_evaluate_formula'2*cos(id1)+cos(id2)+cos(id3)+3*cos(id4)+cos(id5)+cos(id6)+cos(id7)/2*cos(Id6)',@mytab

go 
drop proc usp_evaluate_formula 

drop type type_tab 

drop table tab_formula

Answer 4

Use this Function, It will absolutely working.

CREATE FUNCTION dbo.EvaluateExpression(@list nvarchar(MAX))

RETURNS Decimal(10,2)
AS

BEGIN
Declare @Result Decimal(10,2)
set @Result=1
 DECLARE @pos        int,
       @nextpos    int,
       @valuelen   int

SELECT @pos = 0, @nextpos = 1


WHILE @nextpos > 0
  BEGIN
     SELECT @nextpos = charindex('*', @list, @pos + 1)
     SELECT @valuelen = CASE WHEN @nextpos > 0
                             THEN @nextpos
                             ELSE len(@list) + 1
                        END - @pos - 1

                        Set @Result=@Result*convert(decimal(10,2),substring(@list, @pos + 1, @valuelen))


     SELECT @pos = @nextpos
  END

RETURN @Result
END

You Can use this

Select 10* dbo.EvaluateExpression('10*4.5*0.5')