Database Challenges

--meierruth_michael_tsqlchallenge_21_v4.sql
;with result(Id,digit)
as
(

select Id,case when len(t1.digitrep+'x')=10 then isnull(digit,'X') else 'X' end digit
from

  (
  select Id,n,substring(ScanNumber,(n-1)*3+1,3)+substring(ScanNumber,l*3+2+(n-1)*3+1,3)+substring(ScanNumber,l*6+4+(n-1)*3+1,3) digitrep
  from 
    (
    select Id,
      case when charindex(char(13)+char(10),ScanNumber)<=1 then '???'+char(13)+char(10)+'???'+char(13)+char(10)+'???' else ScanNumber end ScanNumber,
      case when charindex(char(13)+char(10),ScanNumber)<=1 then 1 else (charindex(char(13)+char(10),ScanNumber)-1)/3 end l
    from @t
    ) t1
    join
      (
      select row_number() over (order by t1.n) n
      from (select 0 n union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0) t1
        cross join (select 0 n union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0) t2
        cross join (select 0 n union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0) t3
        -- above handles max number of digits representable in varchar(8000) (8000-4)/9=888 obtainable via 10x10x10 = 1000
        -- to handle even more digits, just add additional cross joins;
        -- for each cross join line you add you need to change the table alias at the end;
      ) t2 on t2.n<=t1.l
  ) t1

  left join

    (
    select digitrep,digit
    from
      (
      select ' _ | ||_|' digitrep,'0' digit
      union
      select '     |  |','1'
      union
      select ' _  _||_ ','2'
      union
      select ' _  _| _|','3'
      union
      select '   |_|  |','4'
      union
      select ' _ |_  _|','5'
      union
      select ' _ |_ |_|','6'
      union
      select ' _   |  |','7'
      union
      select ' _ |_||_|','8'
      union
      select ' _ |_| _|','9'
      union
      select '         ','*'
      union
      select '?????????',''
      ) t3
    ) t2 on t1.digitrep=t2.digitrep
)

select Id,replace((select ''+t2.digit from result t2 where t1.Id=t2.Id for xml path('')),'*',' ') Value
from result t1
group by t1.Id
order by t1.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.