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

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

avatar image

1.8k 3 5

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 24, 2010 at 09:48 AM

Seen: 1639 times

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

Copyright 2016 Redgate Software. Privacy Policy