Database Challenges

--Mueller_Lutz_tsqlchallenge_21_v2.sql
-- submitted by Lutz Mueller on 01/12/2010
-- version b

; WITH cte AS 
(
	SELECT 
		id,
		CASE 
				substring(ScanNumber,      1 + c*3, 3)
			+ substring(ScanNumber,3*n + 3 + c*3, 3)
			+ substring(ScanNumber,6*n + 5 + c*3, 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 b
	FROM 
	@t
	CROSS apply
	(
		SELECT (charindex( CHAR(13),ScanNumber)-1) / 3 AS N
	)x
	CROSS apply
	(
		SELECT Number  AS c 
		FROM master..spt_values 
		WHERE Type='P' 
			AND Number < (charindex(CHAR(13),ScanNumber)-1) / 3

	)y
)
SELECT 
	id as Id,
	replace((SELECT '' + b FROM cte t2 WHERE t2.id = t1.id FOR XML path ('')),' ',' ') as Value
FROM cte t1
GROUP 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.