I saw a wish list blogged at http://omnibuzz-sql.blogspot.com/2006/07/next-version-of-sql-server-wish-list.html. It wanted a sql function that would evaluate an string arithmetic expression in the next version of SQL Server. It turns out that function is already available, sort of, in T-SQL on SQL Server 2005. Here is an example:
DECLARE @x xml
DECLARE @v FLOAT
SET @x = ''
SET @v = @x.value('(1 + 4) div 3', 'FLOAT')
SELECT @v
----------------
1.666666
Unfortunately the following will not work...
DECLARE @x xml
SET @x = ''
DECLARE @e VARCHAR(MAX)
DECLARE @v FLOAT
SET @e = '(1 + 4) div 3'
SET @v = @x.value(@v, 'FLOAT')
SELECT @v
... because value and other XQuery functions require literal XQuery expressions. But all is not lost!
We can weave together a number of .NET technologies in SQL Server 2005 and make our own EvaluateArithmethicExpression scalar valued user defined function. All we have to do is add some basic XPath to an XPathNavigator and regular expressions. XPath itself has builtin support for evaluating arithmetic expressions, albeit some of the operations are names rather than single characters, but a regular expression can clean that up. At the end of this article is a C# function that implements a CLR based user defined function that evaluates string arithmetic expressions. Here is an example of using this function:
SELECT
dbo.EvaluateArithmethicExpression('(50.1234 * 1 + 2)/3')DECLARE
@f FLOATDECLARE
@e VARCHAR(MAX)SET
@e = '(50.1234 * 1 + 2)/3'SET
@f = dbo.EvaluateArithmethicExpression(@e)SELECT
@fSET
@e = '((50.1234 * 1 + 2)/3) % 5'SET
@f = dbo.EvaluateArithmethicExpression(@e)SELECT
@f
----------------------
17.3744666666667
(1 row(s) affected)
----------------------
17.3744666666667
(1 row(s) affected)
----------------------
2.37446666666667
(1 row(s) affected)
You can even access all the math functions in XPath itself...
DECLARE
@f FLOAT
SET
@f = dbo.EvaluateArithmethicExpression('ceiling((50.1234 * 1 + 2)/3) % 5')
SELECT
@f
----------------------
3
Here is the source code for the EvaluateArithmeticExpression
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;
using System.IO;
using System.Text.RegularExpressions;
public class ArithmeticCalculations
{
// dummy doc for XPath eval's
static readonly XPathDocument xpdoc;
static ArithmeticCalculations()
{
StringReader sr = new StringReader("");
// doesn't matter whats in the doc
// we just need something to call evaluate on
xpdoc = new XPathDocument(sr);
sr.Close();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDouble EvaluateArithmethicExpression(SqlString expression)
{
// give me a null and you will get on back
if (expression.IsNull)
{
return SqlDouble.Null;
}
// use regex's to convert typical arithmetic ops to the ones XPath likes
// also force the result to be a double
String useExpression = "number("+Regex.Replace(expression.Value, "/", " div ")+")";
useExpression = Regex.Replace(useExpression, "%", " mod ");
XPathNavigator nav = xpdoc.CreateNavigator();
SqlDouble retval = SqlDouble.Null;
try
{
retval = (Double)nav.Evaluate(useExpression);
}
catch
{
// if its not a plain ol' arithmetic expression
// you will get a null back
retval = SqlDouble.Null;
}
return retval;
}
};
Posted
Jul 27 2006, 10:50 AM
by
dan-sullivan