Wednesday, June 9, 2010

Sum of Digits of a number

Using recursive cte, you can calculate the sum of all the digits of a number easily.
Example, a number 1234, the sum of the digits will be 1 + 2 + 3 + 4 = 10

Here is the function to do that

create function fn_sumdigit 
(
    @number    int
)
returns    int
as
begin
    declare    @sum    int
 
    ;with cte
    as
    (
        select    number    = @number / 10, digit = @number % 10
    
        union all
    
        select    number = number / 10, digit = number % 10
        from    cte
        where    number    > 0
    )
    select    @sum = sum(digit)
    from    cte
 
    return    @sum
end
go
 
-- Example
select    dbo.fn_sumdigit (12345)

this function will make the calculating the EAN check digit much simpler

6 comments:

  1. Can you please explain this code

    ReplyDelete
  2. DECLARE @intValue AS VARCHAR(10) = 100992

    SELECT SUM(CAST(SUBSTRING(@intValue,number,1) AS TINYINT)) SUMOFDIGITS FROM (
    SELECT DISTINCT number FROM
    MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@intValue) ) x

    Pawan Khowal
    MSBISkills.com

    ReplyDelete
  3. Hi Pawan,
    Can you please explain your code

    ReplyDelete

  4. --Explaination below

    --In the first step we are just splitting the into a table using a numbers table (MASTER..SPT_VALUES)

    DECLARE @intValue AS VARCHAR(10) = 100992
    SELECT DISTINCT number , SUBSTRING(@intValue,number,1) FROM
    MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@intValue)


    --From the above query you will get below table

    /*
    number (No column name)
    1 1
    2 0
    3 0
    4 9
    5 9
    6 2
    */

    --After this you just have to get the sum of the new column column

    SELECT SUM(CAST(SUBSTRING(@intValue,number,1) AS TINYINT)) SUMOFDIGITS
    FROM
    (

    SELECT DISTINCT number FROM
    MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@intValue)

    )x

    --Pawan Kumar
    --http://MSBISKILLS.COM

    ReplyDelete