Database Challenges

--Pavel_Krticka_tsqlchallenge_21_v2.sql
with data(Id, ScanNumber, rowLen, i, retval) AS (
select
  Id
  ,replace(convert(varchar(8000), ScanNumber), ' ', '.')  as ScanNumber
  ,charindex(char(13) + char(10), replace(convert(varchar(8000), ScanNumber), ' ', '.')) + 1 as rowLen
  ,0 as i
  ,convert(varchar(8000), '') as retval
from @t
where ScanNumber is not null

union all

select
  Id,
  ScanNumber,
  rowLen,
  i,
  convert(varchar(8000), retval +
  case
    when digit = '.........' then ' '
    when digit = '._.|.||_|' then '0'
    when digit = '.....|..|' then '1'
    when digit = '._.._||_.' then '2'
    when digit = '._.._|._|' then '3'
    when digit = '...|_|..|' then '4'
    when digit = '._.|_.._|' then '5'
    when digit = '._.|_.|_|' then '6'
    when digit = '._...|..|' then '7'
    when digit = '._.|_||_|' then '8'
    when digit = '._.|_|._|' then '9'
    else 'X'
  end)
from
(
  select -- parse number
  Id,
  convert(varchar(8000), ScanNumber) as ScanNumber,
  rowLen,
  i + 3 as i,
  convert(varchar(8000), Stuff(Stuff(Right(Left(ScanNumber, 2*rowLen + i + 3), 2*rowLen + 3), rowLen + 4, rowLen - 3, ''), 4, rowLen - 3, '')) as digit,
  retval
  from data
  where
  i < rowLen - 2
) t1
)

-- final result
select Id, retval as Value
from data
where
i >= rowLen - 2
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.