December 8, 2010 at 9:16 am
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
December 8, 2010 at 9:18 am
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
December 8, 2010 at 9:23 am
This function is to evaluate statement like '4+5*6-3' in sql server.
December 8, 2010 at 9:25 am
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
December 8, 2010 at 9:35 am
Yes, right that will return 31 with that input.
December 8, 2010 at 9:43 am
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
December 8, 2010 at 9:44 am
So what is the question, or suggestion?
December 15, 2010 at 5:08 am
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' )
Nigel
How to post questions
March 8, 2011 at 2:42 pm
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))')
May 14, 2011 at 10:02 am
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)
May 14, 2011 at 10:12 am
You can, however, assign an output variable using sp_executesql, but you can't use sp_executesql in a function. 🙂
March 24, 2012 at 3:59 am
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