Evaluate a string Expression

Question

Post reply

Evaluate a string Expression

ramsatish

Old Hand

Points: 380
More actions
January 15, 2009 at 5:44 am

#130064

Hi,
I have a table which has expression as a column and the data looks similar to the following .
ID (int) Expression (nvarchar data type)
1 8*(1/2)-6
2 278*(1/4)-2
3 81*(3/5) +4
I now have the expression as an nvarchar. (Ex: 8*(1/2)-6)
Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string.
I am unable to cast/convert nvarchar to float/int for evaluating.I can either write a SQl Server procedure or implement it in C#.NET to accomplish the task.
Any sample code would be greatly appreciated.
Thanks,
Sriram Satish

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This is a hack, but it works...

DECLARE @X AS VARCHAR(100)

DECLARE @sql AS nVARCHAR(4000)

SET @X = '8*1/2-6'

SET @sql = 'SELECT ' + @X

PRINT @sql

EXEC(@SQL)

Please notice that I removed the parenthesis to make this evaluation work. I'm sure you can figure out some way to make this work.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

You are probably going to want to write a CLR procedure to handle this. In T-SQL, your biggest problem is going to be the expression evaluation is going to use integer data types because that is what you have in your expression.

So, if you were to use the MSSQL evaluation engine to evaluate 8*(1/2)-6 you are going to get -6

I think what you are after is really 8.0*(1.0/2.0)-6.0 which evaluates to -2

I don't think you can deal with that issue in T-SQL without actually parsing the entire expression.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Doh!!!, I just figured it out

SET @X = '278 * (1 / 4) - 2'

1 / 4 = 0 because both numbers are Ints.

if you use 1.0 instead of 1, that'll work without changing anything else. You'll need to do a search and replace to alter the formulas for this "trick" to work.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

One of the methods is

declare @t table(ID int,Expression nvarchar(100))

insert into @t

select 1,'8*(1/2)-6' union all

select 2,'278*(1/4)-2' union all

select 3,'81*(3/5) +4'

declare @sql varchar(max)

set @sql=''

select @sql=@sql+'select '+cast(id as varchar(10))+','+Expression+' union all ' from @t

set @sql=left(@sql,len(@sql)-9)

exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

Ninja's_RGR'us (1/15/2009)
Doh!!!, I just figured it out
SET @X = '278 * (1 / 4) - 2'
1 / 4 = 0 because both numbers are Ints.
if you use 1.0 instead of 1, that'll work without changing anything else. You'll need to do a search and replace to alter the formulas for this "trick" to work.

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

ramsatish Old Hand Points: 380 More actions · Answer 6

Hi Michael Earl ,

Thank you for the quick reply.

How we are going to achieve this using CLR procedure.

Is there any method/funtion available in CLR(SSIS) like "eval" to evaluate a string mathamatical expression..?

Plz explain with a simple example...

Plz provide me some links Where I can find detailed information..

Thanks in advance

Regards,

Sriram Satish

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

Here is a great sample project for evaluating complex expressions in .Net. It should get you started.

http://www.c-sharpcorner.com/UploadFile/mgold/CodeDomCalculator08082005003253AM/CodeDomCalculator.aspx

Christo Wolmarans Ten Centuries Points: 1170 More actions · Answer 8

Hi

Here is something that you can experiment with (NOTE: You will need a Tally table to make this work. Read this article:http://www.sqlservercentral.com/articles/TSQL/62867/ for a complete explination of it.):

--Create the tables that we will be using

create table #tblExpresstions (ID int, Expression nvarchar(100))

create table #Breakdown (ID int, ChrNum int, Chr varchar(3))

--Add test data

insert into #tblExpresstions

select 1,'8*(1/2)-6' union all

select 2,'278*(1/4)-2' union all

select 3,'81*(3/5) +4';

--Breakdown the test data into individual chatacters

insert into #Breakdown

select ID,N as ChrNum,SUBSTRING(expression,N,1) Chr

from #tblExpresstions

cross join Tally

where N <= LEN(Expression) ;

--Add the .0's where needed

with AddZero as (

select b1.ID, b1.chrnum,cast(b1.chr as varchar) + '.0' Chr

from #Breakdown b1

left join #Breakdown b2 on b2.ChrNum = b1.ChrNum + 1 and b1.ID = b2.ID

where ISNUMERIC(b1.chr) = 1 and ISNUMERIC(b2.chr) = 0

)

--Update the Breakdown table

update #Breakdown

set Chr = AddZero.Chr

from #Breakdown

inner join AddZero on #Breakdown.ID = AddZero.ID and #Breakdown.ChrNum = AddZero.ChrNum

--Create the variables that will loop and select the information

declare @Expression varchar(max), @Cntr int

select @Expression = '', @Cntr = MIN(id) from #tblExpresstions

--Loop through the Breakdown table and reconstruct the expression

while @Cntr <= (select MAX(id) from #tblExpresstions)

begin

select @Expression = @Expression + chr

from #Breakdown

where ID = @Cntr

--select the results

exec('select ''' + @Expression + ''' Expression, ' + @Expression + ' as Value')

select @Cntr = @Cntr + 1, @Expression = ''

end

--delete the temp tables

drop table #tblExpresstions

drop table #Breakdown

Play around with it a bit and let me know how it worked out.

sgmunson SSC Guru Points: 110640 More actions · Answer 9

I don't know for sure, but can't you just add a decimal point prior to every occurrence of an operator? Seems to me that a nested replace ought to be sufficient...

How about:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(YOURFIELD,'+','.+'),'-','.-'),'*','.*'),'/','./'),'^','.^')

Let me know...

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

ramsatish (1/15/2009)
Hi,
I have a table which has expression as a column and the data looks similar to the following .
ID (int) Expression (nvarchar data type)
1 8*(1/2)-6
2 278*(1/4)-2
3 81*(3/5) +4
I now have the expression as an nvarchar. (Ex: 8*(1/2)-6)
Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string.
I am unable to cast/convert nvarchar to float/int for evaluating.I can either write a SQl Server procedure or implement it in C#.NET to accomplish the task.
Any sample code would be greatly appreciated.
Thanks,
Sriram Satish

I'm just curious... why do you need to do such a thing? What are the business reasons for doing this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

ramsatish (1/15/2009)
Hi Michael Earl ,
Thank you for the quick reply.
How we are going to achieve this using CLR procedure.
Is there any method/funtion available in CLR(SSIS) like "eval" to evaluate a string mathamatical expression..?
Plz explain with a simple example...
Plz provide me some links Where I can find detailed information..
Thanks in advance
Regards,
Sriram Satish

Heh... I love it... here's how this conversation goes...

1. How do I do such and such?

2. Some answers come out that aren't quite right

3. Someone suggestions using a CLR

4. Op says basically "Good idea! We'll do THAT!"

5. Op then asks how to do it with a CLR... 😉

Heh... Come on folks. "We don need no stinkin' CLR!" 😛 This is a simple problem and it doesn't need a CLR. Madhivanan was on the right track. All folks have to do is make a simple mod to his good code and you're done...

declare @t table(ID int,Expression nvarchar(100))

insert into @t

select 1,'8*(1/2)-6' union all

select 2,'278*(1/4)-2' union all

select 3,'81*(3/5) +4'

declare @sql varchar(max)

set @sql=''

select @sql=@sql+'select '+cast(id as varchar(10))+','

+REPLACE(REPLACE(REPLACE(Expression,'*','.0*'), '/','.0/'),' ','')

+' union all ' from @t

set @sql=left(@sql,len(@sql)-9)

print @sql

exec(@sql)

If you come up with something beside multiply and divide operators that need a fix, then just add them in the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 12

Jeff,

I hope you're not lumping me in with the "some answers that aren't quite right" group... at least I was the post with the REPLACE idea for the mathematical operators...

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

smunson (1/16/2009)
Jeff,
I hope you're not lumping me in with the "some answers that aren't quite right" group... at least I was the post with the REPLACE idea for the mathematical operators...
Steve
(aka smunson)
:):):)

Heh... no... Funny thing is, I had the thread open for a while and was doing other stuff... Then, I came back to the thread without hitting refresh... I never saw your post in the process. But, I will say, great minds think alike... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve dassin SSCrazy Points: 2396 More actions · Answer 14

Jeff Moden (1/16/2009)
Heh... Come on folks. "We don need no stinkin' CLR!" 😛 This is a simple problem and it doesn't need a CLR.

The Gartner group commissioned a study and with Yale medical school they

found that the top five percent of sql experts were sqldatapaths:) Seriously,

it's 2009 and it shouldn't be necessary to rub two sticks together to get

a fire. There are valid reasons MS tries to quarantine it's sql experts and keep them away from developers (Linq is a type of immunization from sql type staph infections:). I find myself in agreement with this sentiment:

"State of the UNION", January 12, 2009

http://blog.databaseconsultinggroup.com/[/url]

It's time to feed your head some new ideas on how to program where your

sql expertise will serve you well. Learn to use Dataphor with sql server and you'll have the best of two worlds. And they won't collide:)

www.beyondsql.blogspot.com