Database Challenges

--Ruchay_Sohawan_tsqlchallenge_21.sql
/*******************************************************************
	Name:	 Ruchay Sohawan
	Date:	 2010/01/25
	Purpose: Convert numbers from scanner to customer numbers
*******************************************************************/


;WITH NumCTE(ScanId, ScanNumber, CustNum, LineLen, Incr1, Incr2, Incr3) AS 
(
    SELECT	ID, 
			ScanNumber, 
			CAST('' AS VARCHAR(MAX)) as CustNum, 
			CHARINDEX(CHAR(13) + CHAR (10),ScanNumber) AS LineLen, -- Used to control recursive part
			1 as Incr1,												-- Start position of first line										 
			CHARINDEX(CHAR(13) + CHAR (10),ScanNumber)+2 as Incr2,	--Start position of second line	
			CHARINDEX(CHAR(13) + CHAR (10),ScanNumber, CHARINDEX(CHAR(13) + CHAR (10),ScanNumber) +2)+2 as Incr3 --Start position of third line
    FROM @t
    UNION ALL
    SELECT	ScanID, 
			ScanNumber, 
			CAST(CustNum + 
				CASE SUBSTRING(ScanNumber,Incr1,3) + SUBSTRING(ScanNumber, Incr2,3) + SUBSTRING(ScanNumber, Incr3 ,3) 
					WHEN '         ' THEN ' '
					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'
					ELSE 'X'
				END 
			AS VARCHAR(MAX)) as CustNum,
		LineLen, 
		Incr1 + 3,
		Incr2 + 3,
		Incr3 + 3
    FROM NumCTE
	WHERE Incr1 <= LineLen-1 -- Stopping value
)
SELECT ScanId,  CustNum
FROM NumCTE
WHERE Incr1 >= LineLen
ORDER BY ScanID

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.