Database Challenges

--Philippe_Pimenta_tsqlchallenge_21.sql
WITH Test(Id,Line1, Line2, Line3, Value, LastItem) AS
(
	SELECT Id, LEFT(ScanNumber,posline1-1), SUBSTRING(ScanNumber, posline1+1,posline2-posline1-1),SUBSTRING(ScanNumber, posline2+1,posline3-posline2-1),
	CAST('' AS VARCHAR(1000)) as Value,
	0 AS LastItem
	FROM (
	SELECT Id,ScanNumber,
	CHARINDEX(CHAR(10),ScanNumber) as posline1,
	CHARINDEX(CHAR(10),ScanNumber,CHARINDEX(CHAR(10),ScanNumber)+1) as posline2,
	CHARINDEX(CHAR(10),ScanNumber,CHARINDEX(CHAR(10),ScanNumber,CHARINDEX(CHAR(10),ScanNumber)+1)+1) as posline3
	FROM @t) a
	UNION ALL
	SELECT Test.Id,SUBSTRING(Test.Line1, 4, LEN(Test.Line1)-3), SUBSTRING(Test.Line2, 4, LEN(Test.Line2)-3), SUBSTRING(Test.Line3, 4, LEN(Test.Line3)-3),
		CONVERT(VARCHAR(1000),Value + 
			CASE 
			WHEN LEFT(Test.Line1,3) = '   ' /* 1 or 4 or empty*/ THEN
				CASE WHEN LEFT(Test.Line2,3) = '  |' THEN
						CASE WHEN LEFT(Test.Line3,3)='  |' THEN '1' ELSE 'X' END
					 WHEN LEFT(Test.Line2,3) = '|_|' THEN
						CASE WHEN LEFT(Test.Line3,3)='  |' THEN '4' ELSE 'X' END
					 WHEN LEFT(Test.Line2,3) = '   ' THEN
						CASE WHEN LEFT(Test.Line3,3)='   ' THEN ' ' ELSE 'X' END
					 ELSE 'X'
				END
			WHEN LEFT(Test.Line1,3) = ' _ ' THEN/* other */ 
				CASE
					WHEN LEFT(Test.Line2,3) = '| |' THEN /*0*/
						CASE WHEN  LEFT(Test.Line3,3)='|_|' THEN '0'
						ELSE 'X'
						END
					 WHEN LEFT(Test.Line2,3) = ' _|' THEN /*2 or 3*/
						CASE WHEN  LEFT(Test.Line3,3)='|_ ' THEN '2'
							 WHEN  LEFT(Test.Line3,3)=' _|' THEN '3'
						ELSE 'X'
						END
					WHEN LEFT(Test.Line2,3) = '|_ ' THEN /*5 or 6*/
						CASE WHEN  LEFT(Test.Line3,3)=' _|' THEN '5'
							 WHEN  LEFT(Test.Line3,3)='|_|' THEN '6'
						ELSE 'X'
						END

					WHEN LEFT(Test.Line2,3) = '|_|' THEN /* 8 or 9 */
					 CASE WHEN LEFT(Test.Line3,3)='|_|'  THEN '8'  /*8*/
						  WHEN LEFT(Test.Line3,3)=' _|'  THEN '9'  /*9*/
					 ELSE 'X'
					 END
					WHEN LEFT(Test.Line2,3) = '  |' AND LEFT(Test.Line3,3)='  |'  THEN '7' /*7*/ 
				ELSE 'X'
				END
			ELSE 'X'
			END
			),
		CASE WHEN LEN(Test.Line1) <6 THEN 1 ELSE 0 END AS LastItem
	FROM Test
	WHERE LEN(Test.Line1)>=3
)
SELECT Id,Value FROM Test WHERE LastItem = 1
ORDER BY 1

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.