Re: Convert string to Number

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

Date: Fri, 12 Mar 2004 11:46:39 -0500

Here's another approach that works if the formula is fixed:

select x,
      cast(replace(parsename(x,4),'@','.') as decimal(12,4))
      *replace(parsename(x,3),'@','.')
      *replace(parsename(x,2),'@','.')
      /replace(parsename(x,1),'@','.')
from (
  select replace(replace(replace(c1,'.','@'),'*','.'),'/','.') as x
  from formula
) T

SK

Vishal Parkar wrote:

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