Parse a calculation

Question

Post reply

Parse a calculation

Matthew Creasey

Old Hand

Points: 309
More actions
September 7, 2005 at 6:11 pm

#91065

I have 3 fields I am working woth from a table.
2 are numbers, and one is text.
A row for example may be:
Num1: 20
Num2: 10
Text1: *
I need to do the calculation Num1 Text1 Num2, or 20 * 10.
SQL Server compalins if I try to cast "20 * 10" as a number of any type. How can I parse the calculation?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

DECLARE @SQLStr VARCHAR(30)

SELECT @SQLStr = CAST(Num1 As VARCHAR(10)) + Text1 + CAST(Num1 As VARCHAR(10))

FROM A

EXEC ('SELECT ' + @SQLStr)

You may have to add this in a loop for all the records. I am not sure if this can be done for all the records in 1 query

Kindest Regards,

Amit Lohia

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Mathew...you could do something like this:

select Result = case

when Text1 = '*' then (Num1 * Num2)

when Text1 = '+' then (Num1 + Num2)

when Text1 = '-' then (Num1 - Num2)

when Text1 = '/' then (Num1 / Num2)

end

from myTable

..or you could add a "computed column" to your table with the same expression as the case statement - like so:

create table tblParseCalcs
(Num1 int,
Num2 int,
Text1 char(1),
Result as case
when Text1 = '*' then (Num1 * Num2)
when Text1 = '+' then (Num1 + Num2) 
when Text1 = '-' then (Num1 - Num2) 
when Text1 = '/' then (Num1 / Num2)
end)

insert into tblParseCalcs values(20, 10, '*')
insert into tblParseCalcs values(20, 10, '-')
insert into tblParseCalcs values(20, 10, '+')
insert into tblParseCalcs values(20, 10, '/')

select * from tblParseCalcs

2010*200
2010-10
2010+30
2010/2

**ASCII stupid question, get a stupid ANSI !!!**

Matthew Creasey Old Hand Points: 309 More actions · Answer 3

Thanks for your help everyone. I'll test some of this out.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Check out the script section, a much more complete script could do all those operations and much more.