Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-02-13
14
Medium Priority
?
800 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?
0
Comment
Question by:HoggZilla
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 23633616
Hi,

Not an UDF, but you can use dynamic sql:

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

/peter

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23633723
There is a related question where it needs to be a column in a select list so dynamic SQL won't work like that.
0
 
LVL 22

Expert Comment

by:pivar
ID: 23633731
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:HoggZilla
ID: 23633738
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.
0
 
LVL 22

Expert Comment

by:pivar
ID: 23633773
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?
0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23633856
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

0
 
LVL 22

Expert Comment

by:pivar
ID: 23633902
Yes I see. I missed that question. Seems like you already gone through that. 8-)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 23637976
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

0
 
LVL 17

Author Closing Comment

by:HoggZilla
ID: 31546602
Thanks!!! It's worth way more than 500 points. If I make any modifications I will share.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23639700
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...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23651854
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 :-) .
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23651883
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23656152
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

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23656157
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

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question