Database Challenges

--leonid_koyfman_tsqlchallenge_21.sql
;WITH Map (digit,code)
AS(	SELECT ' ',SPACE(9)		UNION ALL
	SELECT '0',' _ | ||_|'	UNION ALL SELECT '1','     |  |' UNION ALL
	SELECT '2',' _  _||_ '	UNION ALL SELECT '3',' _  _| _|' UNION ALL
	SELECT '4','   |_|  |'	UNION ALL SELECT '5',' _ |_  _|' UNION ALL
	SELECT '6',' _ |_ |_|'	UNION ALL SELECT '7',' _   |  |' UNION ALL
	SELECT '8',' _ |_||_|'	UNION ALL SELECT '9',' _ |_| _|'
)
SELECT 
	id
,	ScanNumber=ISNULL(CAST([value]as varchar(max)),'')
FROM 
	@t t1
CROSS APPLY
(	
	SELECT	[*]=ISNULL(Map.digit,'X')
	FROM	@t t2
	CROSS APPLY(SELECT	CrLf		=	NCHAR(13)+NCHAR(10))NL
	CROSS APPLY(SELECT	lineLength	=	CHARINDEX	(CrLf,ScanNumber, 1			)-1	)p1
	CROSS APPLY(SELECT	line2start	=	lineLength + 3								)p2
	CROSS APPLY(SELECT	line3start	=	line2start + lineLength +2					)p3
	CROSS APPLY(SELECT	line1		=	LEFT		(ScanNumber				,lineLength))l1
	CROSS APPLY(SELECT	line2		=	SUBSTRING	(ScanNumber,line2start	,lineLength))l2
	CROSS APPLY(SELECT	line3		=	SUBSTRING	(ScanNumber,line3start	,lineLength))l3
	CROSS APPLY(SELECT	code		=	SUBSTRING	(line1,N*3-2,3)+
										SUBSTRING	(line2,N*3-2,3)+
										SUBSTRING	(line3,N*3-2,3)
				FROM	(	SELECT	Number
							FROM	master..spt_values			
							WHERE	Number BETWEEN 1 AND lineLength/3 --length of ScanNumber
							AND		type='P'
						)Numbers(N)
				)d
	LEFT JOIN	Map  ON d.code=Map.code
	WHERE t2.id=t1.id
	FOR XML PATH(''),type
)T([value])
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.