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 5 12929
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
"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" <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
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
>> 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),
); This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Chris Michael |
last post by:
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...
|
by: Building Blocks |
last post by:
Hi,
All I need is a simle calculate form script which contains this:
A script that can handle text input, radio buttons, checkboxes, and
dropdowns. Each one of these variables will contain a...
|
by: jochen scheire |
last post by:
Is there a way I can calculate a field in a form based on another field in
the same form. When clicking submit, both values should be posted to the
next page. I want to be able to type in a value...
|
by: Sam Kong |
last post by:
Hello!
I wonder if there's a good way to make a function that calculates 2
big numbers (whole numbers) expressed as strings and return a result
string with the value of a number?
For example:...
|
by: Lyners |
last post by:
Quick question. I have some java script that looks like this;
...
|
by: milan.letic |
last post by:
Hello,
I believe that this has been answered somewhere but I cannot find it. I
have 51 textboxes on page. In 50 I can enter value. 51st should tell me
what is average of entered numbers.
As...
|
by: Sam |
last post by:
Hi,
I use C# in my ASP.NET projects. Here's what I need to do: I want to add x
business days to a given date i.e. add 12 business days to today's date. What
is the best, fastest and most...
|
by: kebabkongen |
last post by:
Hi,
I have an XML source which gives me the start time (in the format
hh:mi) of a program and the duration of the program (in minutes).
With XSLT only, I would like to generate the time the next...
|
by: moshiur |
last post by:
I'm a very new programmer.
I have a sorted file and want to calculate delats between the numbers. That is calculate the difference between first number in line 1 and second number in line 2...
|
by: FishVal |
last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL.
So, let us say the problem is to calculate...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |