In the database i have a table with this information:

key_id => 1
key_desc => 43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?

On Jun 21, 12:44 pm, Apaxe <apaxe2000Milh... (AT) gmail (DOT) com> wrote:
Are you saying that the key_desc is a char() field with the value
"43+34+22+12" in it? If that's the case then this IS possible, but
the code is gonna make your head hurt a little bit Let me know and
I'll be happy to work with you on it.

Joshua

Apaxe (apaxe2000Milhoes (AT) gmail (DOT) com) writes:
I will have to confess that I understand about zero of your post. It would
certainly help if you could clarify what you are up to. I would suggest that
you post:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative that explains the business problem you are trying
to address.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 21, 8:05 pm, "jlaust... (AT) gmail (DOT) com" <jlaust... (AT) gmail (DOT) com> wrote:
Hello, Joshua.

Yes, key_desc is a char() field. And i need to extract de sum of the
string. In this case, the value "111".

Thank you.

On Jul 2, 8:11 pm, Apaxe2000 <Apaxe2... (AT) gmail (DOT) com> wrote:

Simply create a temporary table (name it, for example "TheCalculator")
with only ONE record in it (Whatever...) and do something like:

SELECT (123+4)*2 FROM TheCalculator

That'll give you the result of any calculation

Piero

Apaxe :

1. Assuming you have a table and insertions like following ones
(using T-SQL syntax):
CREATE TABLE Test(key_id int, key_desc varchar(100))
INSERT INTO Test SELECT 1, '43+34+22+12' UNION SELECT 2,'34+56+78'

2. Assuming you have a following sequenve view (again using T-SQL)
CREATE VIEW [dbo].[Sequence](seq)
AS
SELECT hundred * 100 + ten * 10 + unit + 1
FROM (SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) AS Units(unit)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) AS Hundreds(hundred)

3. Assuming you have only '+' operation, you can use a select like
following
SELECT Parsed.key_id,
SUM(Parsed.key_vls) sums
FROM (SELECT T.key_id,
CAST (SUBSTRING ('+' + T.key_desc + '+', MAX(S1.seq +
1), (S2.seq - MAX(S1.seq + 1))) AS INTEGER)
FROM Test AS T,
Sequence AS S1,
Sequence AS S2
WHERE SUBSTRING ('+' + T.key_desc + '+', S1.seq, 1) = '+'
AND SUBSTRING ('+' + T.key_desc + '+', S2.seq, 1) = '+'
AND S1.seq < S2.seq
AND S2.seq <= LEN(T.key_desc) + 2
GROUP BY T.key_id, T.key_desc, S2.seq
) AS Parsed(key_id, key_vls)
GROUP BY Parsed.key_id


If you have more operators in your string you have to add to this
query additional checks.
Idea is got from the book "JOE CELKO'S SQL PROGRAMMING STYLE".

Hope this helps,
Andriy

On Jun 21, 8:44 pm, Apaxe <apaxe2000Milh... (AT) gmail (DOT) com> wrote:
This looks similar to one of your other problems see
http://groups.google.com/group/micro...4b73956cc1b7f8

How come you did not clean the data before it got into the database?

Assuming max 4 values (columns) to sum

CREATE TABLE Test(key_id int, key_desc varchar(100))
INSERT INTO Test SELECT 1, '43+34+22+12' UNION SELECT 2,'34+56+78'

select
convert(int,isnull(PARSENAME(replace(key_desc,'+', '.'),4),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+' ,'.'),3),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+' ,'.'),2),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+' ,'.'),1),0))
from Test

or else you can do:

declare c1 cursor for select convert(varchar(9),key_id),key_desc from
Test
open c1
declare @id int, @idc varchar(9), @key_desc varchar(100)
select key_id, 0 'total' into #t1 from Test where 1=2
fetch c1 into @idc, @key_desc
while @@FETCH_STATUS = 0
begin
exec ( 'insert into #t1 select key_id, '+@key_desc+' from Test where
key_id='+@idc)
fetch c1 into @idc, @key_desc
end
close c1
select * from #t1
drop table #t1
deallocate c1
drop TABLE Test