Database Challenges

--Muhammad_AlPasha_tsqlchallenge_21_V5.sql
WITH NumbersStrings(number, number_string) AS
(
   SELECT CAST(' ' AS CHAR(1)), CAST('         ' AS CHAR(9))
    UNION ALL
   SELECT CAST('0' AS CHAR(1)), CAST(' _ | ||_|' AS CHAR(9))
    UNION ALL
   SELECT CAST('1' AS CHAR(1)), CAST('     |  |' AS CHAR(9))
    UNION ALL
   SELECT CAST('2' AS CHAR(1)), CAST(' _  _||_ ' AS CHAR(9))
    UNION ALL
   SELECT CAST('3' AS CHAR(1)), CAST(' _  _| _|' AS CHAR(9))
    UNION ALL
   SELECT CAST('4' AS CHAR(1)), CAST('   |_|  |' AS CHAR(9))
    UNION ALL
   SELECT CAST('5' AS CHAR(1)), CAST(' _ |_  _|' AS CHAR(9))
    UNION ALL
   SELECT CAST('6' AS CHAR(1)), CAST(' _ |_ |_|' AS CHAR(9))
    UNION ALL
   SELECT CAST('7' AS CHAR(1)), CAST(' _   |  |' AS CHAR(9))
    UNION ALL
   SELECT CAST('8' AS CHAR(1)), CAST(' _ |_||_|' AS CHAR(9))
    UNION ALL
   SELECT CAST('9' AS CHAR(1)), CAST(' _ |_| _|' AS CHAR(9))
)
, ScanNumbersWithOffsets AS
(
   SELECT Id, ScanNumber, 1 AS offset1, CHARINDEX(CHAR(10), ScanNumber) + 1 AS offset2,
          2 * CHARINDEX(CHAR(10), ScanNumber) + 1 AS offset3, CHARINDEX(CHAR(10), ScanNumber) / 3 AS number_count
     FROM (SELECT Id, CAST(ScanNumber AS VARCHAR(8000)) AS ScanNumber
             FROM @t) AS T
)
, ScanNumberStrings AS
(
   SELECT SN.Id, SN.ScanNumber, SN.offset1, SN.offset2, SN.offset3, SN.number_count, 9 AS sequence,
          CAST('' AS VARCHAR(8000)) +
             CASE SN.number_count
                  WHEN 0 THEN ''
                  WHEN 1 THEN ISNULL(NS1.number, 'X')
                  WHEN 2 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X')
                  WHEN 3 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X')
                  WHEN 4 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X') + ISNULL(NS4.number, 'X')
                  WHEN 5 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X') + ISNULL(NS4.number, 'X') + ISNULL(NS5.number, 'X')
                  WHEN 6 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X') + ISNULL(NS4.number, 'X') + ISNULL(NS5.number, 'X') +
                              ISNULL(NS6.number, 'X')
                  WHEN 7 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X') + ISNULL(NS4.number, 'X') + ISNULL(NS5.number, 'X') +
                              ISNULL(NS6.number, 'X') + ISNULL(NS7.number, 'X')
                  WHEN 8 THEN ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X') + ISNULL(NS4.number, 'X') + ISNULL(NS5.number, 'X') +
                              ISNULL(NS6.number, 'X') + ISNULL(NS7.number, 'X') + ISNULL(NS8.number, 'X')
                  ELSE ISNULL(NS1.number, 'X') + ISNULL(NS2.number, 'X') + ISNULL(NS3.number, 'X') + ISNULL(NS4.number, 'X') + ISNULL(NS5.number, 'X') +
                       ISNULL(NS6.number, 'X') + ISNULL(NS7.number, 'X') + ISNULL(NS8.number, 'X') + ISNULL(NS9.number, 'X')
             END AS value
     FROM ScanNumbersWithOffsets AS SN
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3, 3)) AS NS1
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3, 3)) AS NS2
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 2, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 2, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 2, 3)) AS NS3
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 3, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 3, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 3, 3)) AS NS4
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 4, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 4, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 4, 3)) AS NS5
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 5, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 5, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 5, 3)) AS NS6
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 6, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 6, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 6, 3)) AS NS7
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 7, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 7, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 7, 3)) AS NS8
          OUTER APPLY
          (SELECT NS.number
             FROM NumbersStrings AS NS
            WHERE NS.number_string = SUBSTRING(SN.ScanNumber, SN.offset1 + 3 * 8, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset2 + 3 * 8, 3) +
                                     SUBSTRING(SN.ScanNumber, SN.offset3 + 3 * 8, 3)) AS NS9

    UNION ALL

   SELECT Id, ScanNumber, offset1, offset2, offset3, number_count, sequence + 1, value + ISNULL(number, 'X')
     FROM ScanNumberStrings AS SNS
          OUTER APPLY
          (SELECT number
             FROM NumbersStrings
            WHERE number_string = SUBSTRING(ScanNumber, offset1 + 3 * sequence, 3) + SUBSTRING(ScanNumber, offset2 + 3 * sequence, 3) + SUBSTRING(ScanNumber, offset3 + 3 * sequence, 3)
              AND sequence <= number_count) AS NS
    WHERE sequence < number_count
)
SELECT Id, value
  FROM ScanNumberStrings
 WHERE sequence >= number_count
 ORDER BY Id
OPTION(MAXRECURSION 0,MAXDOP 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.