SQLTeam.com | Weblogs | Forums

Create new line based on specific character count of a column


#1

Hello all,

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.

Can anyone please suggest some query in SQL.

Thanks In Advance,
Ipsita


#2
; 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