Database Challenges

--leigh_waldie_tsqlchallenge_21.sql
;WITH Numbers(N)
AS
(
SELECT number
FROM master..spt_values WHERE TYPE='P'
),CHARS(number,s)
AS
(
SELECT '0', ' _ | ||_|' UNION
SELECT '1', '     |  |' UNION
SELECT '2', ' _  _||_ ' UNION
SELECT '3', ' _  _| _|' UNION
SELECT '4', '   |_|  |' UNION
SELECT '5', ' _ |_  _|' UNION
SELECT '6', ' _ |_ |_|' UNION
SELECT '7', ' _   |  |' UNION
SELECT '8', ' _ |_||_|' UNION
SELECT '9', ' _ |_| _|' UNION
SELECT ' ', '         '
)
SELECT id,a.s.value('(./text())[1]','nvarchar(max)')
FROM @t 
CROSS APPLY (
			 SELECT COALESCE(CHARS.number,'X') 
			 FROM Numbers 
			 LEFT OUTER JOIN CHARS ON CHARS.s = SUBSTRING(ScanNumber,N*3+1,3)+SUBSTRING(ScanNumber,(N*3)+(CHARINDEX(CHAR(10),ScanNumber)/3)*3+3,3)+SUBSTRING(ScanNumber,(N*3)+(CHARINDEX(CHAR(10),ScanNumber)/3)*6+5,3)
			 WHERE N<(CHARINDEX(CHAR(10),ScanNumber)/3) 
			 FOR XML PATH(''),TYPE
) a(s)

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.