I want to select varchar data like '2 2 3' with result '7'.
I have one column to store data calculation like above sample but need to select the result of the calculation only.
I tried Select Convert(int,'2 2 3') as result but it didn't work.
Please help if you know how to solve it. Thanks
CodePudding user response：
To evaluate an equation you need to use dynamic SQL i.e. build a select statement including the equation and then execute it e.g.
declare @Equation varchar(128) = '2 2 3';
declare @Sql nvarchar(max) = 'select ' @Equation ' as result';
exec sp_executesql @Sql;
Returns:
| result |
|---|
| 7 |
CodePudding user response：
data
declare @a table( vals varchar(max))
insert into @a
(vals) values
('2 3'),
('2 3 4'),
('2 3 4 5'),
('2 3 4 5 7');
use string_split,subquery and cast as follows
select
-- ID,
Sum(Cast(a.value as float)) as result
from (select row_number()
over (
order by (select null)) ID,
vals
from @a) b
CROSS APPLY STRING_SPLIT(vals, ' ') a
group by ID
order by ID
CodePudding user response：
It is better to do such operations (arithmetic and converting) in the programming language part. '2 2 3' this part needs a parsing, you can provide it via programming language or you need to write a function and then call it with eval. This is too risky. And it's bad programming, do the parsing on the programming language instead.