So - more for fun than for any other reason...
Here's a recursive function for Simple Math Eval supporting the four operators. (I know, I know - have a problem, use recursion and now you have two problems... plus the call depth limitation in SQL could become an issue on very involved expressions...)
CREATE FUNCTION [dbo].[fn_SimpleMathEval]
(
@expression VARCHAR(255)
)
RETURNS NUMERIC(18, 6)
AS
BEGIN
DECLARE @result NUMERIC(18, 6)
IF LEN(ISNULL(@expression, '')) = 0
SET @result = NULL
ELSE
BEGIN
SET @result = CASE WHEN CHARINDEX('+', @expression) <> 0
THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression,
CHARINDEX('+',
@expression) - 1))))
+ dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression,
LEN(@expression)
- CHARINDEX('+',
@expression)))))
WHEN CHARINDEX('-', @expression) <> 0
THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression,
CHARINDEX('-',
@expression) - 1))))
- dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression,
LEN(@expression)
- CHARINDEX('-',
@expression)))))
WHEN CHARINDEX('*', @expression) <> 0
THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression,
CHARINDEX('*',
@expression) - 1))))
* dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression,
LEN(@expression)
- CHARINDEX('*',
@expression)))))
WHEN CHARINDEX('/', @expression) <> 0
THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression,
CHARINDEX('/',
@expression) - 1))))
/ dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression,
LEN(@expression)
- CHARINDEX('/',
@expression)))))
WHEN ISNUMERIC(@expression) = 1
THEN CONVERT(NUMERIC(18, 6), @expression)
ELSE NULL
END
END
RETURN @result
END
-wbw