Database Challenges

--jose_chama_tsqlchallenge_21.sql
;with mapping (number, symbol) 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  ' ', '         '
	),
numbers as
(
	select 
		1 as number,
	(	select max(digits)/3
		from ( 
			select charindex(char(10),scannumber) as digits
			from @t ) a
		) as maxdigits
	
	union all
	
	select n.number + 1,
		n.maxdigits
	from numbers n
	where n.number < n.maxdigits
	),
digits as
(
	select
		id,
		substring(scanNumber, (number*3-2), 3)+
		substring(scanNumber, (number*3-1)+charindex(char(10),scannumber)-1, 3)+
		substring(scanNumber, (number*3)+charindex(char(10),scannumber,charindex(char(10),scannumber)+1)-2, 3) as symbol,
		number
	from @t
	cross apply numbers n
	where number between 1 and charindex(char(10),scannumber) / 3
		),
mapped as
(
	select id,
		isnull(cast(n.number as varchar(1)),'X') as Value,
		d.number
	from digits d
	left outer join mapping n
		on d.symbol = n.symbol
),
results as
(
	select 
		id,
		cast(value as varchar(max)) as value,
		number
	from mapped
	where number = 1
	
	union all
	
	select 
		r.id,
		r.value + m.value,
		m.number
	from results r
	inner join mapped m
		on r.id = m.id 
			and r.number = m.number - 1
			
	)
select
	r.id,
	r.value
from results r
inner join @t t
	on r.id = t.id
where number = charindex(char(10),scannumber) / 3
order by 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.