## 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

1. Nice and easy solution..

2. Can you please explain this code

3. 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

4. Hi Pawan,

5. --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