Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

A simple math expression solver Expand / Collapse
Author
Message
Posted Wednesday, November 23, 2011 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:34 AM
Points: 38, Visits: 143
If the specification is for a single operation with an integer on either side ...

CREATE FUNCTION [SimpleMath](@expression VARCHAR(max))
RETURNS INT
AS
BEGIN

DECLARE
@position INT,
@operator CHAR(1),
@left INT,
@right INT,
@result INT

SELECT
@position = PATINDEX('%[*/+-]%', @expression),
@operator = SUBSTRING(@expression, @position, 1),
@left = CONVERT(INT, LTRIM(RTRIM(LEFT(@expression, @position - 1)))),
@right = CONVERT(INT, LTRIM(RTRIM(RIGHT(@expression, LEN(@expression) - (@position + 1))))),
@result = CASE @operator
WHEN '*' THEN @left * @right
WHEN '/' THEN @left / @right
WHEN '+' THEN @left + @right
WHEN '-' THEN @left - @right
END

RETURN @result
END
GO

;WITH [data] ([expression]) AS (
SELECT '1 + 5' UNION ALL
SELECT '10 * 4' UNION ALL
SELECT '15 / 3' UNION ALL
SELECT '100 - 25'
)
SELECT
[expression],
[result] = [dbo].[SimpleMath]([expression])
FROM [data]

Post #1211149
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse