January 15, 2009 at 5:44 am
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
January 15, 2009 at 5:57 am
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.
January 15, 2009 at 6:00 am
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.
January 15, 2009 at 6:01 am
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.
January 15, 2009 at 6:25 am
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)
Failing to plan is Planning to fail
January 15, 2009 at 6:27 am
Ninja's_RGR'us (1/15/2009)
Doh!!!, I just figured it outSET @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
Failing to plan is Planning to fail
January 15, 2009 at 6:43 am
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
January 15, 2009 at 6:50 am
Here is a great sample project for evaluating complex expressions in .Net. It should get you started.
January 16, 2009 at 6:42 am
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.
January 16, 2009 at 1:06 pm
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)
January 16, 2009 at 2:16 pm
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
Change is inevitable... Change for the better is not.
January 16, 2009 at 2:29 pm
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
Change is inevitable... Change for the better is not.
January 16, 2009 at 6:52 pm
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)
January 16, 2009 at 7:22 pm
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
Change is inevitable... Change for the better is not.
January 17, 2009 at 6:19 pm
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:)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply