425,666 Members | 1,784 Online
Need help? Post your question and get tips & solutions from a community of 425,666 IT Pros & Developers. It's quick & easy.

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

 P: n/a 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 Jul 20 '05 #1
5 Replies

 P: n/a 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" 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 Jul 20 '05 #2

 P: n/a "Chris Michael" 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 Jul 20 '05 #3

 P: n/a "Chris Michael" 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 Jul 20 '05 #4

 P: n/a 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 Jul 20 '05 #5

 P: n/a >> 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), ); Jul 20 '05 #6