Tags

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


SQL Puzzle – Find Numbers that satisfies below condition-

Number – ReverseDIGITS(Number) = SUMOfDIGITS(Number) + MULTIPLICATIONOFDIGITS(Number)

Friends, today I discuss a numbers puzzle, Original puzzle link is https://community.oracle.com/thread/668829. Well it was asked in oracle community. Still we shall look how can we can achieve this in SQL. The puzzle says-

You have to find +ve numbers who satisfy following condition-
Number – ReverseDIGITS(Number) = SUMOfDIGITS(Number) + MULTIPLICATIONOFDIGITS(Number)

X – Y = T + Z

where

X as a positive number,
Y is the reverse of X,
T is the sum of each number which X has
Z is the product of each number which X has

Example the number is 63.

63 – 36 = 9 + 18

Expected Output

Id
63
726
8937

Solution # – Here we have to first create 2 functions, one is to find sum of digits and another is find multiplication of digits.

--

--Function to find Multiplication of Digits
CREATE FUNCTION [dbo].[MultiplyDigits] 
(
    @InputString VARCHAR(1000)
)
RETURNS @results TABLE
(
    MultiplicationOutput BIGINT
)
AS
BEGIN

	DECLARE @str AS VARCHAR(1000) = @InputString
 
	;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)
	)
	INSERT @results (MultiplicationOutput)
	SELECT MAX(MultiplicationOutput) MultiplicationOutput FROM CTE		
 
RETURN;
END

GO

--Function to find sum of Digits
CREATE FUNCTION [dbo].[SumofDigits] 
(
    @InputString VARCHAR(1000)
)
RETURNS @results TABLE
(
    SummationOutput BIGINT
)
AS
BEGIN

	DECLARE @intValue AS VARCHAR(10) = @InputString

	INSERT @results (SummationOutput)
	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
	  
RETURN;
END

--Create table and insert some sample data into it
CREATE TABLE testPuzzle
(
	Id BIGINT PRIMARY KEY
)
GO

BEGIN TRAN
	;WITH CTE AS
	(
		SELECT 1 start
		UNION ALL
		SELECT start + 1 FROM CTE
		WHERE start < 10000
	)
	INSERT INTO testPuzzle
	SELECT start FROM CTE
	OPTION (MAXRECURSION 0); 
COMMIT
GO

--Solution

SELECT Id FROM testPuzzle 
WHERE Id > 10 AND Id - REVERSE(Id)  
	= (
	   (SELECT MultiplicationOutput FROM dbo.MultiplyDigits(Id)) 
	   +
	   (SELECT SummationOutput FROM dbo.SumofDigits(Id))
	  )

--

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