SQL Clone
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3059 Visits: 813
Comments posted to this topic are about the item A simple math expression solver
jimbobmcgee
jimbobmcgee
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 916
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1202 Visits: 2001
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
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8024 Visits: 7163
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)[size=2]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.[/size]
bkubicek
bkubicek
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3059 Visits: 813
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 (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

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

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


sherifffruitfly
sherifffruitfly
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3059 Visits: 813
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3059 Visits: 813
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-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 916
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