We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# User Defined Function to evaluate a string containing an expression and return the result.

on
Medium Priority
913 Views
A column contains the following string:

'10000*.875*.0524879'

Need a UDF to return the result.

SELECT udf_EVAL('10000*.875*.0524879')
Returns: 459.269125

Comment: Wouldn't it be useful to have an Expression Evaluation function for SQL that worked similar to SSIS Expressions?
Comment
Watch Question

## View Solution Only

Commented:
Hi,

Not an UDF, but you can use dynamic sql:

exec ('SELECT 10000*.875*.0524879')

/peter

Commented:
There is a related question where it needs to be a column in a select list so dynamic SQL won't work like that.

Commented:
Or if you need to assign it to a variable:

declare @result float
EXECUTE sp_executesql N'SELECT @result=10000*.875*.0524879', N'@result float OUTPUT', @result=@result OUTPUT
SELECT @result
CERTIFIED EXPERT

Commented:
Thanks Peter.
You bring up a good point, it could be done using an exec. The reason for requesting the UDF is to get the result inline with a select, it will be part of a view.

Commented:
If it needs to be a column in a select you can do this

exec ('SELECT 10000*.875*.0524879 AS result, * FROM table')

Or do I misunderstand you?
CERTIFIED EXPERT

Commented:
Here is an example. Here is the related question.
Assuming this row has 3 columns with numeric values
--AMT = 10000
--FX_RT = .875
--VAT_RT = .0524879
A column contains the Formula used to calculate the result
--FORMULA = '[AMT]*[FX_RT]*[VAT_RT]'

``````SELECT
AMT,
FX_RT,
VAT_RT,
FORUMLA,
REPLACE(REPLACE(REPLACE(FORMULA,'[AMT]',AMT),'[FX_RT]',FX_RT),'[VAT_RT]', VAT_RT) EXPRESSION, --'10000*.875*.0524879'
udfEVAL(REPLACE(REPLACE(REPLACE(FORMULA,'[AMT]',AMT),'[FX_RT]',FX_RT),'[VAT_RT]', VAT_RT)) RESULT --459.269125
FROM TABLE
``````

Commented:
Yes I see. I missed that question. Seems like you already gone through that. 8-)
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Well here is a function....

Still need to add in some bracket handling - almost beat me but not giving up... Have to go out today otherwise would stay and fix...

OK, so create the function below, then try with a few tests....

declare @mystr varchar(100)
set @mystr = '10000*.875*.0524879'
select dbo.udf_maths(mystr), dbo.udf_maths('10000*.875*.0524879') , 10000*.875*.0524879

create table #myformula(formula varchar(100))
insert #myformula values ('10000*.875*.0524879')
insert #myformula values ('1-2-3+4+5+6*7')
insert #myformula values ('4*3/6')

select dbo.udf_maths(formula), formula from #myformula

Cheers,
Mark Wills
``````create function udf_maths ( @str varchar(1000) )
returns varchar(1000)
as
begin

-- Original code found and modified years ago. Credit should go to ???
-- basically resolve a simple equation of "value1" operation "value2"
-- and put that answer back into the string and call ourselves again
-- we keep doing that until there are no more operators
-- and we have to distinguish between signed number and operators - just for fun.
-- and Scott can work out how to include math functions in place of simple equations.

declare @Position int                          -- position of the operation
declare @Operator Char(1)                      -- The operation
declare @LHS_Value decimal(32,10)
declare @RHS_Value decimal(32,10)
declare @LHS int
declare @RHS int
declare @New_Equation varchar(1000)

if @str not like '%[-*/+]%' return replace(@str,'S','-')        -- no operators left so nothing left to do

set @Position = patindex('%[*/]%',@str)                         -- need to exhaust multiply / divides
if @Position = 0 set @Position = patindex('%[-+]%',@str)        -- then do add and subtracts

set @Operator = substring(@str,@Position,1)

set @RHS = @Position + patindex('%[-*/+E]%',substring(@str+'E',@Position+1,1000))          -- the 'E' is to give and end point
set @LHS = @Position - patindex('%[-*/+E]%',reverse(substring(@str,1,@Position-1))+'E')

set @RHS_Value = ltrim(rtrim(replace(substring(@str,@Position + 1,@RHS - 1 - @Position),'S','-')))   -- the S is the signed number ie a minus sign
set @LHS_Value = ltrim(rtrim(replace(substring(@str,@LHS + 1,@Position - 1 - @LHS),'S','-')))

set @Answer = case when @operator = '*' then @LHS_Value * @RHS_Value
when @operator = '/' then @LHS_Value / @RHS_Value
when @operator = '+' then @LHS_Value + @RHS_Value
when @operator = '-' then @LHS_Value - @RHS_Value
end

set @New_Equation = case when @LHS=0 then '' else substring(@str,1,@LHS) end +
replace((convert(varchar(20),@Answer)),'-','S') +                   -- get rid of minus from the expression if it belongs to a value
case when @RHS > len(@str) then '' else substring(@str,@RHS,1000) end

if @LHS=0 and @RHS > len(@str) and @str not like '%[-*/+]%' return replace(@str,'S','-')

return dbo.udf_maths(@New_Equation)

end
go
``````

Not the solution you were looking for? Getting a personalized solution is easy.

CERTIFIED EXPERT

Commented:
Thanks!!! It's worth way more than 500 points. If I make any modifications I will share.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Still got more to do... think I will keep playing as well. Hope Scott is reading so he can add in arithmetic functions :) :) and we want to add column names...
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
D'OH, didn't check back on this q so spent time this weekend writing two T-SQL functions:

1) Convert infix expression to postfix

2) Eval postfix expression and return (final) result

Ah well, I have it working and it was a fun exercise anyway :-) .
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I did not include functions in mine but that would be relatively easy (?) to do, I think (?).

I also did not include proper error handling yet, but many edits are possible, because the conversion from infix to postfix allows a fairly detailed analysis.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
would like to see what you have done....  if you want to collaborate before posting, you have my e-mail (or still on my bio).

Similarly I have no error handling.

Functions can be done - I have spent some time looking at how you might be able to add them in... A bit like the difference between a subtract operation versus a negative number, those brackets do get a lot more involved...

Have added in the brackets handling below, and initial negatives...

``````
alter function udf_maths ( @str varchar(1000) )
returns varchar(1000)
as
begin

-- Original code found and modified years ago. Credit should go to ???
-- Totally re-written by Mark Wills for Experts Exchange
-- Now handles brackets and initial negative numbers (as distinct from subtraction)
-- basically resolve a simple equation of "value1" operation "value2"
-- and put that answer back into the string and call ourselves again
-- we keep doing that until there are no more operators
-- and we have to distinguish between signed number and operators - just for fun.
-- and Scott can work out how to include math functions in place of simple equations.

declare @Position int                          -- position of the operation
declare @Operator Char(1)                      -- The operation
declare @LHS_Value decimal(32,10)
declare @RHS_Value decimal(32,10)
declare @LHS int
declare @RHS int
declare @New_Equation varchar(1000)

if patindex('%[*/+(]-%',@str) > 0 set @str = substring(@str,1,patindex('%[*/+(]-%',@str)) + 'S' + substring(@str,patindex('%[*/+(]-%',@str)+2,8000)
if left(@str,1) = '-' set @str = 'S'+substring(@str,2,1000)

if @str not like '%[-*/+(]%' return replace(@str,'S','-')        -- no operators left so nothing left to do

-- can put in math function handling here duplicating bracket handling below, but picking up the math function and resolving between brackets first - maybe lots of if's

set @Position = charindex(')',@str)                               -- need to exhaust inner brackets first

if @position > 0
begin
set @LHS = @position - charindex('(',reverse(substring(@str,1,@Position - 1)))
set @New_Equation = substring(@str,@LHS + 1, @position - @LHS - 1)
set @New_Equation = dbo.udf_maths(@New_Equation)
set @str = stuff(@str,@LHS,@position - @lhs +1,replace(@New_Equation,'-','S'))
return dbo.udf_maths(@str)
end

set @Position = patindex('%[*/]%',@str)                         -- need to exhaust multiply / divides
if @Position = 0 set @Position = patindex('%[-+]%',@str)

set @Operator = substring(@str,@Position,1)

set @RHS = @Position + patindex('%[-*/+E]%',substring(@str+'E',@Position+1,1000))
set @LHS = @Position - patindex('%[-*/+E]%',reverse(substring(@str,1,@Position-1))+'E')

set @RHS_Value = ltrim(rtrim(replace(substring(@str,@Position + 1,@RHS - 1 - @Position),'S','-')))
set @LHS_Value = ltrim(rtrim(replace(substring(@str,@LHS + 1,@Position - 1 - @LHS),'S','-')))

set @Answer = case when @operator = '*' then @LHS_Value * @RHS_Value
when @operator = '/' then @LHS_Value / @RHS_Value
when @operator = '+' then @LHS_Value + @RHS_Value
when @operator = '-' then @LHS_Value - @RHS_Value
end

set @New_Equation = case when @LHS=0 then '' else substring(@str,1,@LHS) end +
replace((convert(varchar(20),@Answer)),'-','S') +                   -- get rid of minus from the expression if it belongs to a value
case when @RHS > len(@str) then '' else substring(@str,@RHS,1000) end

if @LHS=0 and @RHS > len(@str) and @str not like '%[-*/+]%' return replace(@str,'S','-')

return dbo.udf_maths(@New_Equation)

end
go
``````
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Oh, and to test, try :

create table #myformula(formula varchar(100))
insert #myformula values ('10000*.875*.0524879')
insert #myformula values ('1-2-3+4+5+6*7')
insert #myformula values ('4*3/6')
insert #myformula values ('2*(3/6)+(3+(4-2))')
insert #myformula values ('(1-4)/(2*4)+8')
insert #myformula values ('1-4/((2*4)+8)')

select dbo.udf_maths(formula), formula from #myformula

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile