--

--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))
	  )

--

--

/*
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

--

--

--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

--

--

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

--