Split a row to multiple rows every nth character

Question

hi all, i'm using sql server 2005 and i have a data like rw1 "AKU550000001;AKU550000002;AKU550000003;AKU550000004;AKU550000005;AKU550000006;AKU550000007;AKU550000008;AKU550000009;AKU550000010;AKU550000011;AKU550000012;AKU550000013;..........................."

and i want to split it every 78th charcter like rw1 AKU550000001;AKU550000002;AKU550000003;AKU550000004;AKU550000005;AKU550000006; rw2 AKU550000007;AKU550000008;AKU550000009;AKU550000010;AKU550000011;AKU550000012; rw3 AKU550000013;AKU550000014;AKU550000015;AKU550000016;AKU550000017;AKU550000018; rw4 AKU550000019;AKU550000020;AKU550000021;AKU550000022;AKU550000023;AKU550000024; rw5 AKU550000025;AKU550000026;AKU550000027;AKU550000028;AKU550000029;AKU550000030; rw6 AKU550000031;AKU550000032;AKU550000033;AKU550000034;AKU550000035;AKU550000036; rw7 AKU550000037;AKU550000038;AKU550000039;AKU550000040;AKU550000041;AKU550000042; rw8 AKU550000043;AKU550000044;AKU550000045;AKU550000046;AKU550000047;AKU550000048; rw9 AKU550000049;AKU550000050

i have looked the site but didnt get what i need. thanks for your answers :)

Answer 1

This is untested but the idea is to use any tally or numbers table or udf that returns contiguous integers. In this case I used master..spt_values because that exists in sql server 2005.

;with yourTable (Col) as (            
select 'AKU550000001;AKU550000002;AKU550000003;AKU550000004;AKU550000005;AKU550000006;AKU550000007;AKU550000008;AKU550000009;AKU550000010;AKU550000011;AKU550000012;AKU550000013'            
)                   
            
select  substring(yt.col, isNull((number)*78,1), 78)            
       ,v.number            
from    YourTable as yt            
inner join master..spt_values v            
    on  v.type = 'P'            
    and v.number <= len(yt.Col) / 78

EDIT: Tested and corrected

Split a row to multiple rows every nth character

Follow this question

Related Questions