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

Nice and easy solution..

ReplyDeletelike it.

ReplyDeleteCan you please explain this code

ReplyDeleteDECLARE @intValue AS VARCHAR(10) = 100992

ReplyDeleteSELECT 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

Hi Pawan,

ReplyDeleteCan you please explain your code

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