thanks steve and vishal
but my formula will change day by day, sometimes it will looks like
2004/01/01 , 150000*0.9*21/31 +10
2004/02/01 , 150000*0.8*21/28*(1+8/31)-10
2004/03/01 , 150000*0.87*21/31-100+19

hi ricky,

if your formula is static ie (number1 * number2 * number3 / number4) then
you can have query as shown in the following example.

create table formula(c1 varchar(40))
--sample data
insert into formula
select '150000*0.9*21/31' union all
select '150000*0.8*21/28' union all
select '150000*0.87*21/31'

--query

select str(c1 * c2 * c3/c4,10,2)'calc1', c1 * c2 * c3/c4 'calc2'
from
(select cast(left(c1, charindex('*',c1)-1) as decimal(10,2)) c1,
cast(substring (c1,
(charindex('*',c1) + 1),
charindex ('*', c1, charindex('*',c1) + 1) - (charindex('*',c1) + 1)) as
decimal(10,2))c2,
cast(substring (c1, charindex('*',c1,(charindex('*',c1) + 1)) + 1,
(charindex ('/', c1)-1) - charindex('*',c1,(charindex('*',c1) + 1)))as
decimal(10,2)) c3,
cast(right(c1, charindex('/',reverse(c1))-1) as decimal(10,2))c4
from formula) x

--
Vishal Parkar
vgparkar@yahoo.co.in

Ricky,

Then take a look at my first suggestion. Here's a full repro - if you
use it, do some careful testing. It doesn't handle absolutely
everything, and has limits on expression length, number formats, etc.




drop table Ricky
drop function InFixVal
drop table Sequence0_8000
go

-- Keep this table around - it's handy to have!
create table Sequence0_8000 (
i int primary key
)
insert Sequence0_8000
select top 8001 a*10 + b
from (
select top 801 (orderid-10248) as a
from northwind..orders
order by orderid
) X, (
select top 10 (orderid-10248) as b
from northwind..orders
order by orderid
) Y
order by a*10+b

go

-- call this with 1 as the second parameter
create function InFixVal (
@s varchar(300),
@first int
) returns decimal(18,6) as begin

if @first = 1 begin
set @s = replace(@s,'-','"')
end

if @s not like '%[+"*/()]%' return @s
--If @s is just a number, return it

--@s is not just a number, so look for parentheses
declare @left int
set @left = charindex('(',@s)
if @left = 0 begin --no parentheses

declare @op int
set @op = patindex('%[*/]%',@s) --look for * or /
if @op = 0 set @op = patindex('%["+]%',@s)
declare @lft int, @rght int
set @lft = @op-patindex('%[*/"+]%',reverse(substring(@s,1,@op-1))+'+')
set @rght = @op+patindex('%[*/"+]%',substring(@s+'+',@op+1,300))
declare @ys varchar(300)
declare @a decimal(18,6), @b decimal(18,6), @y decimal(18,6)
set @a = substring(@s,@lft+1,@op-1-@lft)
set @b = substring(@s,@op+1,@rght-1-@op)
set @y = case substring(@s,@op,1)
when '+' then @a+@b
when '*' then @a*@b
when '/' then @a/@b
when '"' then @a-@b end

set @ys = case when @lft=0 then '' else substring(@s,1,@lft) end
+ cast(@y as varchar(30))
+ case when @rght > len(@s) then '' else
substring(@s,@rght,300) end
return dbo.InFixVal(@ys,0)

end

declare @right int --position of matching right parenthesis
set @right = (select top 1 S1.i
from Sequence0_8000 S1
join Sequence0_8000 S2
on S1.i >= @left and S2.i <= S1.i
and S1.i <= len(@s)
and substring(@s,S2.i,1) in ('(',')')
group by S1.i
having sum(case substring(@s,S2.i,1)
when '(' then 1 when ')' then -1 end) = 0
order by S1.i)
declare @x decimal(18,6)
declare @xs varchar(300)
set @xs = substring(@s,@left+1,@right-@left-1)
set @x = dbo.InFixVal(@xs,0)
set @s = stuff(@s,@left,@right-@left+1,@x)
return dbo.InFixVal(@s,0)
end
go

create table Ricky (
d datetime,
expression varchar(2000)
)
insert into Ricky values ('20040101' , '150000*0.9*21/31 +100')
insert into Ricky values ('20040201' , '150000*0.8*21/28*(1+8/31)-100')
insert into Ricky values ('20040301' , '150000*0.87*21/31-100+199')


select d, dbo.InFixVal(expression, 1), expression
from Ricky
go



[quoted text, click to view]