How do I calculate the sum of each number in a string of numbers?

[Chris Michael]

I've got a string of numbers, say 123456 (the actually number is 12 digits
long). I need to calculate the sum of each individual number in the string
of numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).

Thanks

[Allan Mitchell]

What about something like

CREATE FUNCTION dbo.f_ReturnSum (@inputVal varchar(12))
RETURNS INT
AS
BEGIN
declare @i TINYINT
declare @returnedVal int
SET @i = 1
SET @returnedVal = 0
WHILE @i <= LEN(@inputVal)
Begin
set @returnedVal = @ReturnedVal + CAST(SUBSTRING(@inputval,@i,1) as
tinyint)
set @i = @i + 1
End
RETURN @returnedVal
END
GO

select dbo.f_ReturnSum('125111111111')

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Chris Michael" <ne**@intomobiles.com> wrote in message
news:c6************@ID-211081.news.uni-berlin.de...

I've got a string of numbers, say 123456 (the actually number is 12 digits
long). I need to calculate the sum of each individual number in the string
of numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).

Thanks

[John Gilson]

"Chris Michael" <ne**@intomobiles.com> wrote in message
news:c6************@ID-211081.news.uni-berlin.de...

I've got a string of numbers, say 123456 (the actually number is 12 digits
long). I need to calculate the sum of each individual number in the string
of numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).

Thanks

Needless to say, this isn't the right representation of digits for summation.
However, try

CREATE TABLE T
(
digits CHAR(12) NOT NULL PRIMARY KEY
)

INSERT INTO T (digits)
VALUES ('123456789123')
INSERT INTO T (digits)
VALUES ('555555555555')

SELECT T.digits,
SUM(CAST(SUBSTRING(T.digits, n, 1) AS INT)) AS digits_sum
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS
I(n)
CROSS JOIN
T
GROUP BY T.digits

digits digits_sum
123456789123 51
555555555555 60

--
JAG

[Chris Michael]

"Chris Michael" <ne**@intomobiles.com> wrote in message
news:c6************@ID-211081.news.uni-berlin.de
|| I've got a string of numbers, say 123456 (the actually number is 12
|| digits long). I need to calculate the sum of each individual number
|| in the string of numbers, so in the example of 123456 the sum would
|| be 21 (1+2+3+4+5+6).

Thanks a lot Allan and John. Exactly what I needed.

--
Chris Michael
www.INTOmobiles.com
Free ringtones/logos
Free mobile alerts
3 months free insurance

[Erland Sommarskog]

John Gilson (ja*@acm.org) writes:

Needless to say, this isn't the right representation of digits for
summation.

I don't know what Chris is up to, but I needed to do this some days
ago. My task was to complete the number with a check digit, and to
do this you need the sum of the digits, each digit multiplied with
a certain weight.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> I need to calculate the sum of each individual number in the string
of numbers, so in the example of 123456 the sum would be 21
(1+2+3+4+5+6). <<

Can I assume this is for a check digit calculation? The best solution
is to store the number as a string and use a summation of CAST()
expressions, to validate the final check digit:

CREATE TABLE Foobar
(..
i CHAR(12) NOT NULL
CHECK (i LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]',
CHECK ((CAST(SUBSTRING(i,1,1) AS INTEGER) + CAST(SUBSTRING(i,2,1) AS
INTEGER) + ..)%10 = CAST(SUBSTRING(i,12,1) AS INTEGER),

);