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”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
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