如何在sql中的数字字符串中添加所有数字 [英] how to add all numbers in a numeric string in sql
问题描述
亲爱的所有
在我的表MyTable中,我有一个名为MyColumn的列,其中包含8位数字值。
(即)
MyColumn
20151201
20151204
20181208
20181219
我的要求是我需要一个名为SumColumn的新列,数据应该是使用此公式的MyColumn值的总和。 br $>
(即)
MyColumn SumColumn
20151201 3
20151204 6
20181208 4
20181219 6
公式为
例如
20181219
(2 + 0 + 1 + 8)+(1+ 2)+(1 + 9)
(11)+(3)+(10)
(1 + 1)+(3)+(1 + 0)
(2)+(3)+(1)
(6)
帮助到达这个。
您可以创建一个将返回的函数添加数字。这是我创建的函数:
创建 FUNCTION fn_AddNumbers( @ inputStr VARCHAR ( 50 ))
RETURNS INT
AS
BEGIN
DECLARE @ finalVal INT
SET @ finalVal = 0
DECLARE @ j TINYINT
DECLARE @ i TINYINT
SET @ j = 1
SET @ i = 1
WHILE ( @ i < = LEN( @ finalVal ))
BEGIN
SET @ j = 1
SET @ finalVal = 0
WHILE ( @ j < = LEN( @ inputStr ))
BEGIN
SET @ finalVal = @ finalVal + CAST(SUBSTRING( @ inputStr , @ j , 1 ) AS TINYINT )
SET @ j = @ j + 1
END
SET @ inputStr = @ finalVal
SET @ i = @ i + 1
END
RETURN @ finalVal
END
然后调用它select语句中的函数。
SELECT dbo.fn_AddNumbers(' 12345') AS ' 数字总和'
祝你好运。
Dear all
In my table MyTable I have a column named MyColumn which contains 8 digit numeric value.
(ie)
MyColumn
20151201
20151204
20181208
20181219
My requirement is I need a new column named SumColumn and the data should be sum of MyColumn values with this formula.
(ie)
MyColumn SumColumn
20151201 3
20151204 6
20181208 4
20181219 6
Formula is
For Example
20181219
(2+0+1+8) + (1+2) + (1+9)
(11) + (3) + (10)
(1+1) + (3) + (1+0)
(2) + (3) +(1)
(6)
Help to arrive this one.
You can create a function which will return the addition of numbers. Here is the function i have created :
CREATE FUNCTION fn_AddNumbers (@inputStr VARCHAR(50)) RETURNS INT AS BEGIN DECLARE @finalVal INT SET @finalVal = 0 DECLARE @j TINYINT DECLARE @i TINYINT SET @j = 1 SET @i = 1 WHILE (@i <= LEN(@finalVal)) BEGIN SET @j = 1 SET @finalVal = 0 WHILE (@j <= LEN(@inputStr)) BEGIN SET @finalVal = @finalVal + CAST(SUBSTRING(@inputStr, @j, 1) AS TINYINT) SET @j = @j + 1 END SET @inputStr = @finalVal SET @i = @i + 1 END RETURN @finalVal END
Then call this function in your select statement.
SELECT dbo.fn_AddNumbers('12345') AS 'Sum Of Numbers'
Good luck.
这篇关于如何在sql中的数字字符串中添加所有数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!