Database Challenges

--regan_wick_tsqlchallenge_21_v4.sql
SELECT
	 Id
	,Value
FROM
	@t ScanNumbers
	CROSS APPLY
		(
		SELECT REPLACE(REPLACE((SELECT
			ISNULL(Value,'X') AS [data()]
		FROM
			(
			SELECT 
				 Id
				,number
				,SUBSTRING(ScanNumber,(0 * DigitCount * 3) + 1 + ((number - 1) * 3),3) + 
				 SUBSTRING(ScanNumber,(1 * DigitCount * 3) + 1 + ((number - 1) * 3),3) + 
				 SUBSTRING(ScanNumber,(2 * DigitCount * 3) + 1 + ((number - 1) * 3),3) AS Digit
			FROM
				(
				SELECT
					 --clean up ScanNumber and calculate digit count
					 Id
					,REPLACE(REPLACE(ScanNumber,CHAR(10),''),CHAR(13),'')		AS ScanNumber
					,FLOOR(CHARINDEX(CHAR(13),ScanNumber) / 3)					AS DigitCount
				FROM
					@t
				) Data1
				CROSS JOIN [master].[dbo].[spt_values] 
			WHERE
				type	= 'P'
			AND	number	BETWEEN 1 AND DigitCount
			AND Id		= ScanNumbers.Id
			) Scans
			LEFT OUTER JOIN
			(
			SELECT
				 ' _ | ||_|'	AS NumStr
				,'0'			AS Value
			UNION ALL
			SELECT
				 '     |  |'	AS  NumStr
				,'1'			AS Value
			UNION ALL
			SELECT
				 ' _  _||_ '	AS NumStr
				,'2'			AS Value
			UNION ALL
			SELECT
				 ' _  _| _|'	AS NumStr
				,'3'			AS Value
			UNION ALL
			SELECT
				 '   |_|  |'	AS NumStr
				,'4'			AS Value
			UNION ALL
			SELECT
				 ' _ |_  _|'	AS NumStr
				,'5'			AS Value
			UNION ALL
			SELECT
				 ' _ |_ |_|' 	AS NumStr
				,'6'			AS Value
			UNION ALL
			SELECT
				 ' _   |  |' 	AS NumStr
				,'7'			AS Value
			UNION ALL
			SELECT
				 ' _ |_||_|' 	AS NumStr
				,'8'			AS Value
			UNION ALL
			SELECT
				 ' _ |_| _|'	AS NumStr
				,'9'			AS Value
			UNION ALL
			SELECT
				 '         '	AS NumStr
				,'S'			AS Value
			) Nums
				ON Scans.Digit = Nums.NumStr
		ORDER BY
			number
		FOR XML PATH ('')),' ',''),'S',' ') AS Value
		) Data3
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.