A simple math expression solver

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ben Kubicek » A simple math expression solver

41 posts, Page 1 of 5

1 2 3 4 5 »»»

A simple math expression solver

Rate Topic

Display Mode

Topic Options

Author

Message

bkubicek

Posted Sunday, November 20, 2011 8:43 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,490, Visits: 388

Comments posted to this topic are about the item A simple math expression solver

Post #1209004

jimbobmcgee

Posted Monday, November 21, 2011 6:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 36, Visits: 556

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

Bill Talada

Posted Monday, November 21, 2011 8:50 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443

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

ScottPletcher

Posted Monday, November 21, 2011 9:37 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1209428

bkubicek

Posted Monday, November 21, 2011 9:53 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,490, Visits: 388

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

jan.novak

Posted Monday, November 21, 2011 10:39 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:13 AM
Points: 20, Visits: 250

But function can use xml data type... Wink

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

Post #1209493

sherifffruitfly

Posted Monday, November 21, 2011 10:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:18 AM
Points: 32, Visits: 407

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

bkubicek

Posted Monday, November 21, 2011 10:59 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,490, Visits: 388

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

bkubicek

Posted Monday, November 21, 2011 11:02 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,490, Visits: 388

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

jimbobmcgee

Posted Monday, November 21, 2011 11:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 36, Visits: 556

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 »

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions