Not an UDF, but you can use dynamic sql:

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

/peter

Solved

Posted on 2009-02-13

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?

'10000*.875*.0524879'

Need a UDF to return the result.

SELECT udf_EVAL('10000*.875*.0524

Returns: 459.269125

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

14 Comments

declare @result float

EXECUTE sp_executesql N'SELECT @result=10000*.875*.052487

SELECT @result

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.

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

Or do I misunderstand you?

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
```

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*

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
```

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 :-) .

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.

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
```

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

By clicking you are agreeing to Experts Exchange's Terms of Use.

Viewers will learn how the fundamental information of how to create a table.

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

Connect with top rated Experts

**8** Experts available now in Live!