SQL Puzzle | Sum of Digits Puzzle

Puzzle Statement

In this puzzle you have to find sum digits from an input number/string

Sample Input

Sample Input could be for example – 100992 or ‘1WW992’

Expected output

Sum Of Digits in this case is 21

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Script

SOLUTION # 1 | Using Numbers Table

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

SOLUTION # 2 | Using Numbers Table (Validation – If characters are present in Input String)

 ```-- DECLARE @intValue AS VARCHAR(10) = '1WW992' SELECT SUM( CASE WHEN SUBSTRING(@intValue,number,1) LIKE '[0-9]' THEN CAST(SUBSTRING(@intValue,number,1) AS TINYINT) ELSE 0 END ) SUMOFDIGITS FROM ( SELECT DISTINCT number FROM MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@intValue) ) x -- ```

Add a comment if you have any other solution in mind. We all need to learn. Enjoy !!!

Keep Learning

Pawan Khowal

Http://MSBISkills.com