Database Challenges

--eugene_paradizov_tsqlchallenge_21.sql
--T-SQL Challenge 21 (Eugene Paradizov)
--Sample data provided on the website was incorrect
--It did not have CRLF at the end of each line
--So, here is the sample data I used to create my solution:
/*
DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(150))

INSERT INTO @t
SELECT	1,--> 000007059
' _  _  _  _  _  _  _  _  _ '+char(13)+char(10)+
'| || || || || |  || ||_ |_|'+char(13)+char(10)+
'|_||_||_||_||_|  ||_| _| _|'+char(13)+char(10)+
'                           '+char(13)+char(10)
UNION 
SELECT  2, --> 490067715
'    _  _  _  _  _  _     _ '+char(13)+char(10)+
'|_||_|| || ||_   |  |  ||_ '+char(13)+char(10)+
'  | _||_||_||_|  |  |  | _|'+char(13)+char(10)+
'                           '+char(13)+char(10)
UNION
SELECT	3, --> 680X68279
' _  _  _     _  _  _  _  _ '+char(13)+char(10)+
'|_ |_|| || ||_ |_| _|  ||_|'+char(13)+char(10)+
'|_||_||_||_||_||_||_   | _|'+char(13)+char(10)+
'                           '+char(13)+char(10)
UNION
SELECT	4, --> 490867716
'    _  _  _  _  _  _     _ '+char(13)+char(10)+
'|_||_|| ||_||_   |  |  ||_ '+char(13)+char(10)+
'  | _||_||_||_|  |  |  ||_|'+char(13)+char(10)+
'                           '+char(13)+char(10)
UNION
SELECT	5, --> X90867716
'    _  _  _  _  _  _     _ '+char(13)+char(10)+
'| ||_|| ||_||_   |  |  ||_ '+char(13)+char(10)+
'  | _||_||_||_|  |  |  ||_|'+char(13)+char(10)+
'                           '+char(13)+char(10)
UNION 
SELECT 6, --> 012345678
' _     _  _     _  _  _  _ '+char(13)+char(10)+
'| |  | _| _||_||_ |_   ||_|'+char(13)+char(10)+
'|_|  ||_  _|  | _||_|  ||_|'+char(13)+char(10)+
'                           '+char(13)+char(10)
UNION 
SELECT 7, --> 012345678911
' _     _  _     _  _  _  _  _       '+char(13)+char(10)+
'| |  | _| _||_||_ |_   ||_||_|  |  |'+char(13)+char(10)+
'|_|  ||_  _|  | _||_|  ||_| _|  |  |'+char(13)+char(10)
UNION 
SELECT 8, --> 0
' _ '+char(13)+char(10)+
'| |'+char(13)+char(10)+
'|_|'+char(13)+char(10)
UNION 
SELECT 9, null;
*/

/*
	SOLUTION
*/
--below is the definition for the recursive CTE
--the lng2, ln
with cte1(id, lng1, lng2, lng3, lne1, lne2, lne3, cnum, is_final_number, actual_number) as (
	--below is the anchor sql for the recursive CTE
	--it generates the first digit of a given number, based on 3 lines
	select lines.id, lines.lng1, lines.lng2cf, lines.lng3cf, lines.lne1, lines.lne2, lines.lne3, cast(1 as int), cast(case when 1=lng1/3 then 1 else 0 end as int), cast(
		case 
			case substring(lne1,1,3) when '   ' then 0 when '  |' then 1 when ' _ ' then 2 when ' _|' then 3 when '|  ' then 4 when '| |' then 5 when '|_ ' then 6 when '|_|' then 7 else 8 end + 
			(16*case substring(lne2,1,3) when '   ' then 0 when '  |' then 1 when ' _ ' then 2 when ' _|' then 3 when '|  ' then 4 when '| |' then 5 when '|_ ' then 6 when '|_|' then 7 else 8 end) +
			(256*case substring(lne3,1,3) when '   ' then 0 when '  |' then 1 when ' _ ' then 2 when ' _|' then 3 when '|  ' then 4 when '| |' then 5 when '|_ ' then 6 when '|_|' then 7 else 8 end)
		when 1874 then '0' when 272 then '1' when 1586 then '2' when 818 then '3' when 368 then '4' when 866 then '5' when 1890 then '6' when 274 then '7' when 1906 then '8' when 882 then '9' when 0 then ' '
		else 'X' end as varchar(max))actc
	from
		--below is the 4th sql;
		--it calculates the actual 3 lines that compose the numbers, based on baseline length and CRLF positions
		(select l_3.id, l_3.lng1, l_3.lng1cf, l_3.lng2cf, l_3.lng3cf, substring(t.ScanNumber,1,l_3.lng1) lne1, substring(t.ScanNumber,l_3.lng1cf+2,l_3.lng1) lne2, substring(t.[ScanNumber],l_3.lng2cf+2,l_3.lng1) lne3 from
			--below is the 3rd sql;
			--it calculates the actual poistion of the third CRLF
			--(or assumes the end-of-line to be that position, if CRLF is not found);
			(select l_3s.Id, l_2.lng1, l_2.lng1cf, l_2.lng2cf, case when charindex(char(13)+char(10),l_3s.ScanNumber,l_2.lng2cf+2)-1<0 then len(l_3s.ScanNumber)+1 else charindex(char(13)+char(10),l_3s.ScanNumber,l_2.lng2cf+2) end lng3cf from @t l_3s join
				--below is the 2nd sql;
				--it calculates the actual poistion of the second CRLF;
				(select l_2s.Id, l_1.lng1, l_1.lng1cf, charindex(char(13)+char(10),l_2s.ScanNumber,l_1.lng1cf+1) lng2cf from @t l_2s join
					--below is the 1st sql (baseline);
					--it calculates the length of the first line (down to a number divisble by 3);
					--and calculates the actual position of CRLF;
					(select l_1s.Id, cast((floor((charindex(char(13)+char(10),l_1s.ScanNumber)-1)/3.0))*3 as int) lng1, charindex(char(13)+char(10),l_1s.ScanNumber) lng1cf from @t l_1s) l_1
					on l_2s.Id=l_1.Id where l_1.lng1>2) l_2
				on l_3s.Id=l_2.Id where l_2.lng2cf>2) l_3
			join @t t on l_3.id=t.id where l_3.lng3cf>2) lines
	union all
	--below is the recursive member of the CTE
	--it generates the rest of the digits of a given number, based on 3 lines
	select cte1.id, cte1.lng1, cte1.lng2, cte1.lng3, cte1.lne1, cte1.lne2, cte1.lne3, cte1.cnum+1, cast(case when cnum+1=lng1/3 then 1 else 0 end as int), cte1.actual_number+cast(
		case 
			case substring(lne1,(cte1.cnum)*3+1,3) when '   ' then 0 when '  |' then 1 when ' _ ' then 2 when ' _|' then 3 when '|  ' then 4 when '| |' then 5 when '|_ ' then 6 when '|_|' then 7 else 8 end + 
			(16*case substring(lne2,(cte1.cnum)*3+1,3) when '   ' then 0 when '  |' then 1 when ' _ ' then 2 when ' _|' then 3 when '|  ' then 4 when '| |' then 5 when '|_ ' then 6 when '|_|' then 7 else 8 end) +
			(256*case substring(lne3,(cte1.cnum)*3+1,3) when '   ' then 0 when '  |' then 1 when ' _ ' then 2 when ' _|' then 3 when '|  ' then 4 when '| |' then 5 when '|_ ' then 6 when '|_|' then 7 else 8 end)
		when 1874 then '0' when 272 then '1' when 1586 then '2' when 818 then '3' when 368 then '4' when 866 then '5' when 1890 then '6' when 274 then '7' when 1906 then '8' when 882 then '9' when 0 then ' '
		else 'X' end as varchar(max))
	from cte1 join @t t on cte1.id=t.id and (cte1.cnum+1)*3<=cte1.lng1
)
--below is the sql to get the values from the CTE;
--only the rows with [is_final_number] set to 1 are returned;
select Id, actual_number [Value] from cte1 where is_final_number=1 order by id

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.