DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t
SELECT	1,--> 000 007 059
' _  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|
                           
' UNION 
SELECT  2, --> 490 067 715
'    _  _  _  _  _  _     _ 
|_||_|| || ||_   |  |  ||_ 
  | _||_||_||_|  |  |  | _|
                           
' UNION
SELECT	3, --> 680 X68 279
' _  _  _     _  _  _  _  _ 
|_ |_|| || ||_ |_| _|  ||_|
|_||_||_||_||_||_||_   | _|
                           
' UNION
SELECT	4, --> 490 867 716
'    _  _  _  _  _  _     _ 
|_||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
'  UNION
SELECT	5, --> X90 867 716
'    _  _  _  _  _  _     _ 
| ||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
' 
UNION 
SELECT 6, --> 012 345 678
' _     _  _     _  _  _  _ 
| |  | _| _||_||_ |_   ||_|
|_|  ||_  _|  | _||_|  ||_|
                           
' 

/*
=============================================================================================
CREATE DATE: 03/06/2010
 CREATED BY: SETH PHELABAUM
    PURPOSE: TSQL Challenge 21 - Reading, recognizing and processing graphical representation of digits.
     ISSUES: 
      NOTES:	
Revision History
Date	 By		Change Made
-------- ---  	-------------------------------------
=============================================================================================
*/
--   ' _ ','   ',' _ ',' _ ','   ',' _ ',' _ ',' _ ',' _ ',' _ '
--   '| |','  |',' _|',' _|','|_|','|_ ','|_ ','  |','|_|','|_|'
--   '|_|','  |','|_ ',' _|','  |',' _|','|_|','  |','|_|',' _|'

; WITH 
	-- Tally table Gen
		t1 AS (SELECT 1 N UNION ALL SELECT 1 N), 
		t2 AS (SELECT 1 N FROM t1 x, t1 y),        
		t3 AS (SELECT 1 N FROM t2 x, t2 y),        
		Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y),
	CRS AS (
		SELECT	Id, ScanNumber, 
				CHARINDEX(CHAR(10), ScanNumber, CHARINDEX(CHAR(10), ScanNumber)+1) CR2,
				CHARINDEX(CHAR(10), ScanNumber) CR
		FROM @t),
	Lines AS (
		SELECT Id, CR, SUBSTRING(ScanNumber, 1, CR) L1, SUBSTRING(ScanNumber, CR+1, CR) L2, SUBSTRING(ScanNumber, CR2+1, CR) L3
		FROM CRS),
	Matches (Digit, Code)
		 AS ( 	SELECT	'0', ' _ | ||_|' UNION ALL
				SELECT	'1', '     |  |' UNION ALL
				SELECT	'2', ' _  _||_ ' UNION ALL
				SELECT	'3', ' _  _| _|' UNION ALL
				SELECT	'4', '   |_|  |' UNION ALL
				SELECT	'5', ' _ |_  _|' UNION ALL
				SELECT	'6', ' _ |_ |_|' UNION ALL
				SELECT	'7', ' _   |  |' UNION ALL
				SELECT	'8', ' _ |_||_|' UNION ALL
				SELECT	'9', ' _ |_| _|' UNION ALL
				SELECT  'S', '         '
				),
	LSS (Id, N, L1, L2, L3, Digits, RN)
		AS (	SELECT Id, ((3*N) - 3), L1, L2, L3, 
						SUBSTRING(L1,((3*N) - 2), 3) + SUBSTRING(L2,((3*N) - 2), 3) + SUBSTRING(L3,((3*N) - 2), 3),
						ROW_NUMBER() OVER (PARTITION BY Id ORDER BY N)
				FROM Lines, Tally
				WHERE N <= Lines.CR/3),
	Matchup (ID, RN, MatchDigit) 
		AS (	SELECT Id, RN, ISNULL(Digit,'X')
				FROM LSS L
					LEFT JOIN Matches M ON L.Digits = M.Code)
					
SELECT ID, REPLACE((SELECT '' + MatchDigit 
			FROM Matchup M 
			WHERE M.Id = T.ID
			ORDER BY RN
			FOR XML PATH('')),'S',' ') Value
FROM @t T