Re: Convert string to Number

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 21:02:08 +0530

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

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Graphical Union-Query Builder?
    ... If you are limiting the input in each of the sub-queries in your Union ... Use the filtered queries as the input to your Union query: ... Most "functional IT users" will not know anything about SQL. ... I didn't know that fields of subsequent queries in a Union could have ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: How to get a distinct count of result set of multople table joins?
    ... since the UNION syntax removes duplicate rows automatically. ...   "SORT " in your execution plan. ... The base query is an outer join. ...
    (comp.databases.oracle.misc)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)