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]

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

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" <news@intomobil es.com> wrote in message
news:c6tnk5$g00 3r$1@ID-211081.news.uni-berlin.de...[color=blue]
> 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
>
>[/color]

[John Gilson]

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

"Chris Michael" <news@intomobil es.com> wrote in message
news:c6tnk5$g00 3r$1@ID-211081.news.uni-berlin.de...[color=blue]
> 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[/color]

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(SUBSTR ING(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]

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

"Chris Michael" <news@intomobil es.com> wrote in message
news:c6tnk5$g00 3r$1@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

https://www.INTOmobiles.com

Free ringtones/logos
Free mobile alerts
3 months free insurance

[Erland Sommarskog]

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

John Gilson (jag@acm.org) writes:[color=blue]
> Needless to say, this isn't the right representation of digits for
> summation.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

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

>> 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(SUBSTRIN G(i,1,1) AS INTEGER) + CAST(SUBSTRING( i,2,1) AS
INTEGER) + ..)%10 = CAST(SUBSTRING( i,12,1) AS INTEGER),

);

[Sem]

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

Hey, I was looking into recursive properties of the whole number set and I put together a formula that will do exactly what you need is it fairly simple and works every time
x-1
y = x( x - ------ )
2


X is the number in question and y is the sum of x through 1

If you don't mind me asking what are you using it for

Sem Gallaugher