Sum of digits of a number in sql server without using traditional loops, e.g. while

Question

var1 = 12345
output = 1+2+3+4+5 = 15

I tried the following

Declare @var1 int   = 12345,
        @Length int = len(12345)

;with SUMM as 
(
SELECT SUBSTRING(CAST(@var1 AS VARCHAR) ,1,@Length)%10 N
UNION ALL
SELECT SUBSTRING(CAST(@var1 AS VARCHAR) ,1,@Length-1))%10 N
FROM SUMM
WHERE @Length <= len(@var1)
)
SELECT SUM(N) FROM SUMM;

Please help me achieve this with cte and any other methods other than the traditional loop are welcome

Answer 1

This is a fun exercise, if a little pointless.

DECLARE @var1 int   = 12345;

WITH i AS (
    SELECT @var1 / 10 n, @var1 % 10 d

    UNION ALL

    SELECT n / 10, n % 10
    FROM i
    WHERE n > 0
)
SELECT SUM(d)
FROM i;

Answer 2

SELECT 
SUM(CAST (SUBSTRING(STRING.B, V.NUMBER+1, 1)AS INT) )
FROM 
(SELECT '12345' B) STRING
JOIN MASTER..SPT_VALUES V ON V.NUMBER < LEN(STRING.B)
WHERE V.TYPE = 'P'

Answer 3

Try this code:

DECLARE @var1 VARCHAR(30)=12345;

WITH t4
     AS (SELECT 0 x
         UNION ALL
         SELECT 0
         UNION ALL
         SELECT 0
         UNION ALL
         SELECT 0),
     Numbers
     AS (SELECT Row_number()
                  OVER(
                    ORDER BY a.x) AS Num
         FROM   T4 a
                CROSS JOIN T4 b
                CROSS JOIN T4 c)
SELECT Sum(CASE
             WHEN Substring(@var1, Num, 1) LIKE '[0-9]' THEN Cast(Substring(@var1, Num, 1) AS INT)
             ELSE 0
           END)
FROM   Numbers
WHERE  Numbers.Num <= Len(@var1); 

RESULT

sum_of_numbers
15