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 Sunday, November 20, 2011 8:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:38 AM
Points: 1,810, Visits: 479
Comments posted to this topic are about the item A simple math expression solver
Post #1209004
Posted Monday, November 21, 2011 6:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:24 AM
Points: 62, Visits: 755
Interesting stuff.

Performance-wise, how does fn_reverse_charIndex hold up against (LEN(@expr) - CHARINDEX(@char, REVERSE(@expr)) + 1)

And, seeing as your fn_simplemath function appears only to handle one expression at a time, how would it (injection aside) hold up against:

CREATE TABLE #result (res NUMERIC);
INSERT INTO #result (res) EXEC ('SELECT ' + @expr)
SELECT res FROM #result
DROP TABLE #result

J.
Post #1209223
Posted Monday, November 21, 2011 8:50 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:18 PM
Points: 128, Visits: 823
Pretty cool!
I've been interested in writing a tokenizer and recursive descent parser for handling user entered expressions with variables (column names). But, I knew it would take a week or more to get it to be extensible. Anyone up for that challenge?
Post #1209356
Posted Monday, November 21, 2011 9:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 1,977, Visits: 2,926
Interesting code.

But can't use just do either of these:

DECLARE @result TABLE (value decimal(24, 4))
INSERT INTO @result
EXEC('SELECT 3.0 / 4 + 3.2')
SELECT * FROM @result

Or:
DECLARE @value decimal(24, 4)
EXEC sp_executesql N'SELECT @value = 3.0 / 4 + 3.2', N'@value decimal(24, 4) OUTPUT', @value OUTPUT
PRINT @value

?


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1209428
Posted Monday, November 21, 2011 9:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:38 AM
Points: 1,810, Visits: 479
In my article I state that I considered using some form of exec. The issue is I needed this to be done in a function. Functions cannot use exec.

Thanks,
Ben
Post #1209453
Posted Monday, November 21, 2011 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:45 AM
Points: 20, Visits: 336
But function can use xml data type...

declare @x xml=''
select @x.query('10+2*3.2') as result

Post #1209493
Posted Monday, November 21, 2011 10:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 4:36 AM
Points: 33, Visits: 412
Why not just sqlclr + any of the brazillion c# expression parsers?

https://www.google.com/#sclient=psy-ab&hl=en&source=hp&q=c%23+math+expression+parser&pbx=1&oq=c%23+math+express&aq=0&aqi=g2g-C1g-jC1&aql=&gs_sm=e&gs_upl=0l0l1l79l0l0l0l0l0l0l0l0ll0l0&bav=on.2,or.r_gc.r_pw.r_cp.,cf.osb&fp=ea0e067728996111&biw=1231&bih=652

More generally, why chew up db cycles (affecting all db users) with this at all, rather than put the dotnet code in the client app itself (affecting only the user who needs it)?
Post #1209504
Posted Monday, November 21, 2011 10:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:38 AM
Points: 1,810, Visits: 479
When I try to put your code into a function passing in a string variable I get an error:
The argument 1 of the xml data type method "query" must be a string literal.

That seems to suggest it won't work in a function since it has to be a static string not a dynamic variable.

I like the idea though. Thanks for the post.

Ben
Post #1209508
Posted Monday, November 21, 2011 11:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:38 AM
Points: 1,810, Visits: 479
I don't have anything against the sql clr. In this case since I just needed a simple math expression solver, it seemed a bit over kill to call the clr to solve a simple math expression. My guess would be that unless the math expression is quite long this function will probably out perform the clr and the CPU impact would be minimal. If I had the requirement to handle more complex math expressions then the clr with .net code would be a clear solution. Thanks for your post.

Ben
Post #1209513
Posted Monday, November 21, 2011 11:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:24 AM
Points: 62, Visits: 755
ScottPletcher(11/21/2011)
Interesting code.
But can't use just do either of these:


Looks like you and I had the same idea...

To answer my own question, I found that integer division was a bit of a pain when run through an INSERT/EXEC, even when storing in a DECIMAL field. You have to either format the numbers with decimal points (as you have done), or cast each one in turn.

Of course, that is an known issue/design-choice with SQL Server's handling of integers, but I imagine a well-functioning expression parser would overcome this by explicitly casting the numbers to a non-integral numeric type.

The OP's code appears to cast each operand to NUMERIC(18, 6), which would overcome the issue, at least to that precision.
Post #1209526
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse