String expression for evaluating a number

Question

I need to write a user-defined TSQL function that will take a string and return a number.

I will call a function like dbo.EvaluateExpression('10*4.5*0.5') , should return the number 22.5

Can someone help me write this EvaluateExpression function.

I am currently using the CLR function, which I need to avoid.

Edit1

I know that this can be done using a stored procedure, but I want to call this function in some ex statements: select 10* dbo.EvaluateExpression('10*4.5*0.5')

I also have about 400,000 formulas like this.

Edit2

I know that we can do this using the osql.exe internal function, as described here . But because of the resolution settings, I also cannot use this.

Answer 1

I do not think this is possible in a custom function.

You can do this in a stored procedure, for example:

 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, such as exec or sp_executesql , is not allowed in user-defined functions.

Answer 2

Disclaimer : I am the owner of the Eval SQL.NET project

For SQL 2012+, you can use Eval SQL.NET, which can be run with SAFE permission.

Performance is great (better than UDF) and takes precedence and parentheses. In fact, almost the entire C # language is supported.

You can also specify a parameter in 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

Use this function, it will absolutely work.

 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') 

Answer 4

I think you should write your own func using recursion. It may be useful to use reverse polish notation

Answer 5

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

In 2012, I wrote a solution that can evaluate any type of mathematical formula using SQL SERVER. The solution can process any formula with N variables:

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

The solution I found myself was as follows:

Suppose I have n parameters used to calculate a formula, each of these parameters is stored on one row in one data table. - The data table containing n rows for use in the formula is called tab_value - I need to save n values ​​found in n rows (in tab_values) in one row in one new table using the SQL cursor - for this 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 will build a SQL script containing the formula for calculating the formula

Here, after the full script, I hope you find it useful, you can ask me about it.

The procedure uses as input: -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 a 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), so how to start - CAMEL GAZZA [e-mail] 05/09/2016

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

remove from @tab_values ​​paste into @tab_values ​​(id, val) select * from @values

declare @id as int declare @val as decimal (10,2)

if it doesn’t exist (select 1 from sysobjects, where name = 'tab_formula') create a table tab_formula (identifier identifier (1,1), formula nvarchar (1000)) if it doesn’t exist (select 1 from tab_formula where formula = @formula) insert into the values ​​of tab_formula (formulas) (@formula)

declare with cursor to select id, val from @tab_values ​​declare @ script as nvarchar (4000) open c fetch c in @id, @val while @@ fetch_status = 0 begin set @ script = 'if does not exist (select 1 from syscolumns c inner join sysobjects o to c.id = o.id where o.name = '' tab_formula '' and c.name = '' id '+ convert (nvarchar (3), @id) +' '') change table tab_formula add id '+ convert (nvarchar (3), @id) +' decimal (10,2) 'print @scriptexec (@script) set @ script =' update tab_formula set id '+ convert (nvarchar (3) , @id) + '=' + convert (nvarchar (10), @val) + 'where formula =' '' + @formula + '' '' print @script exec (@script) extract c in @id, @ val end close c deallocate c

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

end

go

declare @mytab as type_tab; insert into @mytab (val) (1.56) values; insert into @mytab (val) (15) values; insert into @mytab (val) (25) values; insert into @mytab (val) (32) values into @mytab (val) values ​​(17) into @mytab (val) values ​​(33) insert into @mytab (val) values ​​(37.9)

Exec usp_evaluate_formula'cos (ID1) + Cos (ID2) + Cos (id3) + Cos (ID4) + Cos (ID5) + Cos (ID6) + Cos (ID7) / COS (ID6) ", @mytab

go drop proc usp_evaluate_formula drop type type_tab drop table tab_formula