SQL Puzzle | Multiplication of Digits Puzzle

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 &lt; 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 &lt; 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

4 thoughts on “SQL Puzzle | Multiplication of Digits Puzzle”

Amr Massoud said:

December 26, 2016 at 4:48 PM

DECLARE @t VARCHAR(256) = ‘W912W’;
declare @i INT = 0;
DECLARE @ii INT = 1;

WHILE @i < LEN(@t)
BEGIN
IF ISNUMERIC(SUBSTRING(@t,@i,1)) = 1
SET @ii = cast(SUBSTRING(@t,@i,1) AS INT) * @ii
SET @i = @i + 1;
END

SELECT @ii

LikeLike

Gregor Kralj said:

May 15, 2019 at 2:29 PM

declare @input varchar(10) = ‘1234’;
with cte as
(
select ISNULL(TRY_CAST(SUBSTRING(@input, 1, 1) as int),0) as number, 1 as position
union all
select ISNULL(TRY_CAST(SUBSTRING(@input, position + 1, 1) as int),0) as number, position + 1 as position
from cte
where position < LEN(@input)
)
select EXP(SUM(LOG(number))) from cte

LikeLiked by 1 person

jerryjames08outlookcom said:

February 25, 2020 at 6:57 AM

Hope this solution works :-

;WITH CTE1
AS
(
SELECT @intValue as Val,CAST(IIF(ISNUMERIC(SUBSTRING(@intValue,1,1))=1,SUBSTRING(@intValue,1,1),1) AS bigint) AS MUL,1 AS NUM
UNION ALL
SELECT Val,Mul*IIF(ISNUMERIC(SUBSTRING(Val,NUM+1,1))=1,SUBSTRING(Val,NUM+1,1),1) AS MUL,NUM+1 AS NUM
FROM CTE1
WHERE NUM

LikeLike

jerryjames08@outlook.com said:

April 5, 2020 at 7:44 AM

—-Used decimal(32,0) as it is max length for numeric

;WITH CTE
AS
(
SELECT @intValue AS txt
,SUBSTRING(@intValue,1,1) AS sub
,IIF(TRY_CAST(SUBSTRING(@intValue,1,1) AS Decimal(38,0)) is null OR SUBSTRING(@intValue,1,1) =’0′,1,CAST(SUBSTRING(@intValue,1,1) AS Decimal(38,0))) AS nos
,1 AS num
UNION ALL
SELECT txt
,SUBSTRING(txt,NUM+1,1) AS sub
,nos*IIF(TRY_CAST(SUBSTRING(txt,NUM+1,1) AS Decimal(38,0)) is null OR SUBSTRING(txt,NUM+1,1) =’0′,1,SUBSTRING(txt,NUM+1,1)) AS nos
,NUM+1 AS NUM
FROM CTE
WHERE NUM+1

LikeLike

Improving my SQL BI Skills

SQL Puzzle | Multiplication of Digits Puzzle

4 thoughts on “SQL Puzzle | Multiplication of Digits Puzzle”

Leave a comment Cancel reply

Share this

Related

4 thoughts on “SQL Puzzle | Multiplication of Digits Puzzle”

Leave a comment Cancel reply