SQL Puzzle | Multiplication of Digits Puzzle

Puzzle Statement

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

Sample Input

Sample Input could be for example – 912 or ‘W912W’

Expected output

Multiplication Of Digits in this case is 18

Rules/Restrictions

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

SOLUTION # 1 | Using RECURSIVE CTE

 ```-- --Trial # 1st Trial DECLARE @str AS VARCHAR(100) = '912' ;WITH CTE AS ( SELECT 1 start , CASE WHEN SUBSTRING(@str,1,1) LIKE '[0-9]' THEN CAST(SUBSTRING(@str,1,1) AS TINYINT) ELSE 0 END MultiplicationOutput UNION ALL SELECT start + 1 start , MultiplicationOutput * CASE WHEN SUBSTRING(@str,start+1,1) LIKE '[0-9]' THEN CAST(SUBSTRING(@str,start+1,1) AS TINYINT) ELSE 0 END MultiplicationOutput FROM CTE WHERE start < DATALENGTH(@str) ) SELECT MAX(MultiplicationOutput) MultiplicationOutput FROM CTE --#- 2nd Trial DECLARE @str AS VARCHAR(100) = 'W912W' ;WITH CTE AS ( SELECT 1 start , CASE WHEN SUBSTRING(@str,1,1) LIKE '[0-9]' THEN CAST(SUBSTRING(@str,1,1) AS TINYINT) ELSE 1 END MultiplicationOutput UNION ALL SELECT start + 1 start , MultiplicationOutput * CASE WHEN SUBSTRING(@str,start+1,1) LIKE '[0-9]' THEN CAST(SUBSTRING(@str,start+1,1) AS TINYINT) ELSE 0 END MultiplicationOutput FROM CTE WHERE start < DATALENGTH(@str) ) SELECT MAX(MultiplicationOutput) MultiplicationOutput FROM CTE -- ```

SOLUTION # 2 | Using Numbers Table & SELECT

 ```-- DECLARE @intValue AS VARCHAR(10) = 265 SELECT EXP(SUM(LOG(CAST(SUBSTRING(@intValue,number,1) AS TINYINT)))) MultiplicationOfDigits 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