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  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  10 AND Id - REVERSE(Id)  
	= (
	   (SELECT MultiplicationOutput FROM dbo.MultiplyDigits(Id)) 
	   +
	   (SELECT SummationOutput FROM dbo.SumofDigits(Id))
	  )

--

Solution by Lonnberg, David

--

/*
X-Y=T+Z
Y is the reverse of X
T is the sum of digits X
Z is the product of digits in X

*/
/*
create table #n(n bigint not null primary key clustered);
;with gen as ( --used to generate numbers
	select num from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)
), nums as (
	select (ones.num + (10 * tens.num) + (100 * hundreds.num) + (1000 * thousands.num) + (10000 * tenthousands.num) + (100000 * hundredthousands.num)) as n
	from gen ones, gen tens, gen hundreds, gen thousands, gen tenthousands, gen hundredthousands
)
insert #n
select n from nums
where n >= 1

select * from #n;
drop table #n;
*/

;with d as (
	select n, cast(reverse(cast(n as varchar(10))) as int) as [rev], (n - cast(reverse(cast(n as varchar(10))) as int)) as [diff]
	from #n
), s as (
	select 1 as pos, n, cast(substring(cast(n as varchar(10)), 1, 1) as int) as digit 
	from d 
	union all

	select s.pos + 1, n, cast(substring(cast(n as varchar(10)), s.pos + 1, 1)as int)
	from s
	where pos < datalength(cast(n as varchar(10)))
), p as (
	select pos, n, digit 
	from s 
	where pos = 1
	union all

	select s.pos, s.n, s.digit * p.digit
	from s
	join p on s.pos = p.pos + 1 and s.n = p.n
), sap as (
	select
	n, 
	sod = (select sum(digit) from s where s.n = p.n),
	pod = digit
	from p
	where pos = (select max(pos) from p as p2 where p.n = p2.n)
)
select sap.n 
from sap
join d on sap.n = d.n
where d.diff = (sap.sod + sap.pod) 
order by sap.n

--

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