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