Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A simple math expression solver


A simple math expression solver

Author
Message
bkubicek
bkubicek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 757
Comments posted to this topic are about the item A simple math expression solver
jimbobmcgee
jimbobmcgee
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 914
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.
Bill Talada
Bill Talada
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 1810
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?
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6674
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
bkubicek
bkubicek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 757
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
jan.novak
jan.novak
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 354
But function can use xml data type... ;-)

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


sherifffruitfly
sherifffruitfly
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 427
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)?
bkubicek
bkubicek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 757
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
bkubicek
bkubicek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 757
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
jimbobmcgee
jimbobmcgee
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 914
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search