Database Challenges

--Pavel_Krticka_tsqlchallenge_21_v3.sql
with data(Id, row1, row2, row3, i, retval) AS (
select
  Id
  ,convert(varchar(max), replace(
    case
      when charindex(char(13) + char(10), ScanNumber) > 0 then
        convert(varchar(max), left(ScanNumber, charindex(char(13) + char(10), ScanNumber)-1))
      else ScanNumber
    end
   , ' ', '.')) as row1
  ,convert(varchar(max), replace(
    case
      when charindex(char(13) + char(10), ScanNumber) > 0 then
        case
          when charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) > 0 then
            convert(varchar(max), substring(ScanNumber, charindex(char(13) + char(10), ScanNumber) + 2, charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) - charindex(char(13) + char(10), ScanNumber) - 2))
          else
            convert(varchar(max), substring(ScanNumber, charindex(char(13) + char(10), ScanNumber) + 2, datalength(ScanNumber)))
        end
      else ''
    end
   , ' ', '.')) as row2
  ,convert(varchar(max), replace(
    case
      when (charindex(char(13) + char(10), ScanNumber) > 0) and (charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) > 0) then
        case
          when charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) + 1) > 0 then
            convert(varchar(max), substring(ScanNumber, charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) + 2, charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) + 1) - charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) - 2))
          else
            convert(varchar(max), substring(ScanNumber, charindex(char(13) + char(10), ScanNumber, charindex(char(13) + char(10), ScanNumber) + 1) + 2, datalength(ScanNumber)))
        end
      else ''
    end
   , ' ', '.')) as row3
  ,0 as i
  ,convert(varchar(max), '') as retval
from @t
where ScanNumber is not null

union all

select -- parse number
Id,
row1,
row2,
row3,
i + 3,
case
  when (substring(row1, 1 + i, 3) = '...') then -- ' ',1,4
    case
      when (substring(row2, 1 + i, 3) = '...') then -- ' '
        case
          when (substring(row3, 1 + i, 3) = '...') then retval + ' '
          else retval + 'X'
        end
      when (substring(row2, 1 + i, 3) = '..|') then -- 1
        case
          when (substring(row3, 1 + i, 3) = '..|') then retval + '1'
          else retval + 'X'
        end
      when (substring(row2, 1 + i, 3) = '|_|') then -- 4
        case
          when (substring(row3, 1 + i, 3) = '..|') then retval + '4'
          else retval + 'X'
        end
      else retval + 'X'
    end
  when (substring(row1, 1 + i, 3) = '._.') then -- 0,2,3,5,6,7,8,9
    case
      when (substring(row2, 1 + i, 3) = '|.|') then -- 0
        case
          when (substring(row3, 1 + i, 3) = '|_|') then retval + '0'
          else retval + 'X'
        end
      when (substring(row2, 1 + i, 3) = '._|') then -- 2,3
        case
          when (substring(row3, 1 + i, 3) = '|_.') then retval + '2'
          when (substring(row3, 1 + i, 3) = '._|') then retval + '3'
          else retval + 'X'
        end
      when (substring(row2, 1 + i, 3) = '|_.') then -- 5,6
        case
          when (substring(row3, 1 + i, 3) = '._|') then retval + '5'
          when (substring(row3, 1 + i, 3) = '|_|') then retval + '6'
          else retval + 'X'
        end
      when (substring(row2, 1 + i, 3) = '..|') then -- 7
        case
          when (substring(row3, 1 + i, 3) = '..|') then retval + '7'
          else retval + 'X'
        end
      when (substring(row2, 1 + i, 3) = '|_|') then -- 8,9
        case
          when (substring(row3, 1 + i, 3) = '|_|') then retval + '8'
          when (substring(row3, 1 + i, 3) = '._|') then retval + '9'
          else retval + 'X'
        end
      else retval + 'X'
    end
  else retval + 'X'
end
from data
where
i < len(row1)
)

-- final result
select Id, retval as Value
from data
where
i >= len(row1)
order by id
OPTION (MAXRECURSION 32767,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.