CLR Eval function

steve.laforge (10/30/2012)

I want to be able to pass a string and have it perform the equation within the string. For example, if I pass it "(7 + 1) / 3)", I owuld expect to get back 2.67.

I have seen several posts on here, but none of them appear to work. So I attempted to create one myself. This is what I have in my stringEval function:

Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function stringEval(ByVal equation As String) As SqlDecimal
Dim table As New DataTable
Dim eqObject As Object
eqObject = table.Compute(equation, "")
Dim results As SqlDecimal = SqlDecimal.Parse(eqObject.ToString())
Return results
End Function
End Class

I then try to test it with the following query:

declare @results decimal(18,2)
select @results = dbo.stringEval('(7 + 1) / 3')
select @results as results

When I run this in debug mode in VS2010, I see that the variable 'results' contains 2.6666666667 when it gets to the return statement. However, the query returns '3.00'. I feel like I am so very close to what everyone is asking for, but nobody seems to have an answer for.

Can anyone help me resolve this?

Many thanks!

P.S., yes I know that this is VB instead of C#. I started my effort thinking that I could use the VB Eval function, but then realized that went away with VB.Net.

DECLARE @v VARCHAR(100); -- local variable for demo, but this could be an stored proc input param too
SET @v = '((7 + 1) / 3.0)'

----------------------
-- the actual code

-- setup a SELECT which will force the engine to eval your math problem
SET @v = 'SELECT ' + @v;

-- setup a table variable to capture your result
DECLARE @result TABLE (value VARCHAR(100));

-- execute the SELECT to solve the math problem and cature the result in the table variable
INSERT INTO @result (value)
EXEC (@v);

-- show the answer
SELECT *
FROM @result;

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic.Strings

Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function stringEval(ByVal equation As String) As SqlString

Dim table As New DataTable
Dim eqObject As Object
eqObject = table.Compute(equation, "")
Dim results As SqlString = SqlDecimal.Parse(eqObject.ToString()).ToString()
Return results
End Function

End Class