I have one field as LONGDESC.
I want to output as:
1st 5 character of longdesc in 1st line
2nd 5 character i.e 6-10 in 2nd line
3rd 5 character i.e 11-15 in 3rd line
so on till we have any charachter left in LONGDESC field.
; with
num as
(
select n = 0
union all
select n = n + 1
from num
where n < 100
),
tbl as
(
select LONGDESC = 'The quick fox jump over the lazy fox' union all
select LONGDESC = 'This is a long description' union all
select LONGDESC = '123456789012345678901'
)
select *, substring(LONGDESC, (n * 5) + 1, 5)
from tbl t
inner join num n on n.n <= len( LONGDESC ) / 5
order by t.LONGDESC, n.n