Database Challenges

--Arumugam_Thiraviam_TSQLChallenge_21.sql
; WITH numCTE AS(
SELECT ID, number,
           CASE SUBSTRING(ScanNumber, 3*number-2, 3) + 
                SUBSTRING(ScanNumber, CHARINDEX(CHAR(10), ScanNumber, 3*number-2) + 3*number - 2, 3) + 
                SUBSTRING(ScanNumber, CHARINDEX(CHAR(10), ScanNumber, CHARINDEX(CHAR(10), ScanNumber, 3*number-2) + 3*number - 2) + 3*number - 2, 3)
           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 Digit
from @t, master.dbo.spt_values n
where n.type = 'P'
and number BETWEEN 1 AND CHARINDEX(CHAR(10), ScanNumber, 0)  /3
) SELECT n1.Id, REPLACE(Value, ' ', ' ') FROM numCTE n1
CROSS APPLY (SELECT Digit +''
               FROM numCTE n2
              WHERE n1.Id = n2.Id
              ORDER BY number 
              FOR XML PATH('')) X (Value)
              WHERE n1.number = 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.