Evaluate a string Expression

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

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

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