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.

Medium Priority
913 Views
Last Modified: 2012-05-06
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

Commented:
Hi,

Not an UDF, but you can use dynamic sql:

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

/peter

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

Author

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

Author

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

Open in new window

Commented:
Yes I see. I missed that question. Seems like you already gone through that. 8-)
Topic Advisor, Page Editor
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 @Answer 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

Open in new window

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

Ask the Experts
CERTIFIED EXPERT

Author

Commented:
Thanks!!! It's worth way more than 500 points. If I make any modifications I will share.
Mark WillsTopic Advisor, Page Editor
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...
Scott PletcherSenior 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 :-) .
Scott PletcherSenior 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.
Mark WillsTopic Advisor, Page Editor
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 @Answer 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

Open in new window

Mark WillsTopic Advisor, Page Editor
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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.