Re: Convert string to Number

From: Steve Kass (skass_at_drew.edu)
Date: 03/13/04 

Date: Fri, 12 Mar 2004 22:42:31 -0500

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

ricky wrote:

>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 +100
>2004/02/01 , 150000*0.8*21/28*(1+8/31)-100
>2004/03/01 , 150000*0.87*21/31-100+199
>
>
>

Relevant Pages