Database Challenges

--andrey_myasnikov_tsqlchallenge_21_v1.sql
;WITH CTE (ID,ScanNumber,DigID,[Value])
AS
(SELECT	ID, 
		ScanNumber,	
		0 as DigID,  -- Sequential number of a digit in the ScanNumber string
		CAST('' as VARCHAR(MAX)) as [Value]
FROM @t

UNION ALL

SELECT	CTE.ID,
		CTE.ScanNumber,
		CTE.DigID+1,
		CTE.[Value] + 
		CASE
		CAST(
				(	SUBSTRING(CTE.ScanNumber, 3*CTE.DigID+1, 3)+  -- Line 1 of a digit number DigID
					SUBSTRING(CTE.ScanNumber, 3*CTE.DigID+CHARINDEX(CHAR(13),CTE.ScanNumber)+2, 3)+  -- Line 2 of a digit number DigID
					SUBSTRING(CTE.ScanNumber, 3*CTE.DigID+CHARINDEX(CHAR(13),CTE.ScanNumber)*2+3, 3) -- Line 3 of a digit number DigID
			 ) as VARCHAR(MAX)   
			)
			WHEN ' _ | ||_|' THEN '0'
			WHEN '     |  |' THEN '1'
			WHEN ' _  _||_ ' THEN '2'
			WHEN ' _  _| _|' THEN '3'
			WHEN '   |_|  |' THEN '4'
			WHEN ' _ |_  _|' THEN '5'
			WHEN ' _ |_ |_|' THEN '6'
			WHEN ' _   |  |' THEN '7'
			WHEN ' _ |_||_|' THEN '8'
			WHEN ' _ |_| _|' THEN '9'
			WHEN '         ' THEN ' '
			ELSE 'X' END

FROM CTE 
WHERE CTE.DigID < CHARINDEX(CHAR(13),CTE.ScanNumber) / 3)  -- Limit to number of digits in the string

SELECT ID,[Value]
FROM CTE
WHERE DigID = CHARINDEX(CHAR(13),ScanNumber) / 3  -- Return fully populated values only
ORDER BY ID, [Value]

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.