Eval function in sql

  • create function EvalSQLStatement(@strStatement as varchar(1000))

    returns decimal(12,2)

    as

    begin

    Declare @expr as varchar(1000);

    Declare @expr1 as varchar(20);

    Declare @expr2 as varchar(20);

    Declare @expr3 as varchar(50);

    Declare @var1 as decimal(12,2);

    Declare @operator as varchar(2);

    Declare @position as integer;

    Declare @NrofChar as integer;

    Declare @positionOriginal as integer;

    set @expr =@strStatement

    if CHARINDEX('/',@expr) > 0

    set @operator = '/'

    else if CHARINDEX('*',@expr) > 0

    set @operator = '*'

    else if CHARINDEX('-',@expr) > 0

    set @operator = '-'

    else if CHARINDEX('+',@expr) > 0

    set @operator = '+'

    else

    set @operator = 'no'

    set @NrofChar = 0

    set @positionOriginal = CHARINDEX(@operator,@expr)

    --select @position

    set @expr1=''

    set @position = @positionOriginal

    --select substring(@expr,@position,1)

    while (ASCII(substring(@expr,@position+1,1))>47 OR ASCII(substring(@expr,@position+1,1))=46)

    begin

    set @expr1 = @expr1 + substring(@expr,@position+1,1)

    set @position = @position + 1

    set @NrofChar = @NrofChar + 1

    end

    set @position = @positionOriginal-1

    set @expr2=''

    while ASCII(substring(@expr,@position,1))>47 OR substring(@expr,@position,1) = '.'

    begin

    set @expr2 = substring(@expr,@position,1)+@expr2

    set @position = @position - 1

    set @NrofChar = @NrofChar + 1

    end

    if @operator = '/'

    set @expr3 = cast(cast((cast(@expr2 as decimal(9,2)) / cast(@expr1 as decimal(9,2))) as decimal(10,2)) as varchar(50))

    else if @operator = '*'

    set @expr3 = cast((cast(@expr2 as decimal(9,2)) * cast(@expr1 as decimal(9,2))) as varchar(50))

    else if @operator = '-'

    set @expr3 = cast((cast(@expr2 as decimal(9,2)) - cast(@expr1 as decimal(9,2))) as varchar(50))

    else if @operator = '+'

    set @expr3 = cast((cast(@expr2 as decimal(9,2)) + cast(@expr1 as decimal(9,2))) as varchar(50))

    if @operator ='no'

    begin

    set @var1 = cast(@expr as decimal(9,2));

    return @var1

    end

    else

    begin

    set @expr = stuff(@expr,case @position when 0 then 1 else @position+1 end ,@NrofChar+1,@expr3)

    set @expr=dbo.EvalSQLStatement(@expr)

    end

    set @var1 = cast(@expr as decimal(9,2));

    return @var1;

    end

  • What's it meant to do?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • This function is to evaluate statement like '4+5*6-3' in sql server.

  • As in, it's meant to return 31 with that input? (Assuming it uses standard arithmetic priorities.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes, right that will return 31 with that input.

  • I'd suggest submitting it to Steve (the site's editor) by using the Write for Us link (to the left), for the scripts section, or the articles section. In either case, include a description of what it's for and how it works.

    Assuming, of course, that it's something you created, not something you copied from somewhere else.

    Or do you have a question about it?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • So what is the question, or suggestion?

  • There's already a far easier way to do this with EXEC.

    EXEC('PRINT 4+5*6-3' )

    or

    EXEC('SELECT 4+5*6-3' )

  • A easier way using only EXEC :

    EXEC('SELECT CAST(

    (1.66 * 300 / 1170)

    + (1.75 * 220 / 1170)

    + (1.791 * 0 / 1170)

    + (1.6666 * 100 / 1170)

    + (1.96 * 350 / 1170)

    + (1.8716 * 100 / 1170)

    + (1.6666 * 0 / 1170)

    + (1.44 * 100 / 1170)

    AS DECIMAL(18,4))')

  • Using EXEC is easier, but you can't hold the result of EXEC in a variable. This is a no-no:

    set @result = EXEC('SELECT 4+5*6-3' )

    The submitted function will allow this for mathematical expressions:

    set @result = dbo.eval(@expression)

  • You can, however, assign an output variable using sp_executesql, but you can't use sp_executesql in a function. 🙂

  • Really helpful, but it is not running if i put brackets

    like (10*8)/8

Viewing 12 posts - 1 through 11 (of 11 total)

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