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