x

Split a row to multiple rows every nth character

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 :)

more ▼

asked Jan 24, 2010 at 09:48 AM in Default

user-749 (google) gravatar image

user-749 (google)
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jan 24, 2010 at 06:32 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22

asked: Jan 24, 2010 at 09:48 AM

Seen: 1385 times

Last Updated: Jan 24, 2010 at 09:48 AM