Function - Add sum of digits
-
I need a way (preferably a user defined function) to accept a number such as 462304.92 and calculate the sum of the digits (in this case, 4+6+2+3+0+4+9+2 = 30), ignoring the decimal symbol and then returning the rightmost digit from the total (30) which is 0 in my example above. Is there a quick and easy way to do this? Any help or assistance would be appreciated.
-
Here is a quick, simplified script, (I am assuming five decimal places will suffice and the length of the values is no greater than ten places):
DECLARE @OriginalValue varchar(25),
@Sum integer,
@SingleNumber integer,
@Counter integer,
@Length integer
SELECT @OriginalValue = REPLACE((SELECT STR( 462304.92, 10, 5)), CHAR(46), '')
SELECT @Counter = 1
SELECT @Sum = 0
SELECT @Length = (SELECT LEN( @OriginalValue))
WHILE @Counter <= @Length
BEGIN
SELECT @SingleNumber = CONVERT( integer, SUBSTRING( @OriginalValue, @Counter, 1))
SELECT @Sum = @Sum + @SingleNumber
SELECT @Counter = @Counter + 1
END
SELECT @Sum
I am guessing you may need this for an accounting program for verifying invoice numbers or something along those lines. If this is the case, I have a stored procedure I would be happy to share with you; it deals with alphanumeric characters as well.
Depending upon the size of your table, a stored procedure "may" work better than a function.
I wasn't born stupid - I had to study.
-
create function dbo.digit ( @n varchar(40))
returns int
as
begin
declare @result int, @cntr int
select @result = 0, @n = replace(rtrim(ltrim(@n)),'.','')
set @cntr = len(@n)
While @cntr > 0
begin
set @result = @result + cast (Substring(@n,@cntr,1) as int)
set @cntr = @cntr -1
end
return cast(right(cast(@result as varchar(100)),1) as int)
end
select '12345' Data, dbo.digit('12345') R
union all select '462304.92',dbo.digit('462304.92')
why do you need that ?
* Noel -
Check this out... it runs 19 times faster than the function solution (and could be even faster with less casts) :
dbcc dropcleanbuffers
go
Select OrderId, ProductId, UnitPrice, SUM(CAST(SUBSTRING(CAST(UnitPrice AS VARCHAR(40)), PkNumber, 1) AS SMALLINT)) AS TOTAL from NORTHWIND.dbo.[Order Details] cross join Ideal.dbo.Numbers WHERE PkNumber <= LEN(CAST(UnitPrice AS VARCHAR(40))) AND SUBSTRING(CAST(UnitPrice AS VARCHAR(40)), PkNumber, 1) BETWEEN '1' AND '9' GROUP BY OrderId, ProductId, UnitPrice
go
dbcc dropcleanbuffers
go
Select OrderId, ProductId, UnitPrice, dbo.digit (CAST(UnitPrice AS nVarchar(40))) AS Total from NORTHWIND.dbo.[Order Details]
go
--create the Numbers table :
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
--I use this table for string operations as well, but in this case we could stop sonner.
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
-
Thanks Noel, but your function did not seem to work for my purposes. I am looking for a function to sum all the values in the number 462304.94. ie. the sum of all values for the 462304.94 = 4+6+2+3+0+4+9+4 = 32. Can you suggest another way?
-
Ooops....sorry Noel...forgot to answer your question. I am trying to generate a simple checksum for the number that is being passed into a stored procedure that I have created.
-
Noeld's fix :
change the return line to this :
return @result
-
Cory,
If all you need is a check sum (which I supposed it would be) then
this is your function:
select checksum(Field)
or
select binary_checksum(Field)
Ah and my function DO add all numbers!
Remi,
Good job but there is a catch (you are returning the whole sum) not the last Digit
* Noel -
Sorry Noeld... didn't reread the initial post after Corey said it didn't work. Looks like he should reread his own posts too
.Now I assume that the checksum will be even faster than what I coded so I won't correct my version to include this ahem "new" requirement
. -
Thanks Noel. You are da man!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply