Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Advertisements