Splitting a string into several rows

Question

Post reply

Splitting a string into several rows

gary.townsend

Valued Member

Points: 69
More actions
July 17, 2012 at 8:45 am

#259082

All,
I'd be grateful if you could give some advice on how to achieve a solution to the following :-
I have a string of 900 chars (a free format diary notes field). I need to split this string several rows in one field of varchar(65) and with an ascending line no field. So as follows
Original Text Field
"Rang the client and agreed they would begin to pay off £x off their current arrears amount,
This will begin next month and continue until arrears are zero"
New layout
LineNo TextField
1 Rang the client and agreed they would begin to pay off £x off the
2 their current arrears amount, This will begin next month and conti
3 nue until arrears are zero
Hope this makes sense guys!
Many thanks,
Gary

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

This can be done using a Tally Table.

Thanks to Wayne Sheffield for the concept on doing this.

declare @LongText varchar(255) = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero.'

declare @Len int = 65

SELECT N as RowNum, SUBSTRING(@LongText, ((N - 1) * @Len) + 1, @Len) as SplitVal

FROM Tally

WHERE N - 1 <= 8000 / @Len

AND SUBSTRING(@LongText, ((N - 1) * @Len) + 1, @Len) > ''

ORDER BY N;

To see what this Tally table and how to use it please read Jeff Moden's article.

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gary.townsend Valued Member Points: 69 More actions · Answer 2

Many thanks for your help - I shall read and hopefully digest!

CapnHector SSCoach Points: 16143 More actions · Answer 3

Here is mine, slightly different way to calculate the bits

DECLARE @STR VARCHAR(8000)

SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ID, SUBSTRING(@STR,N,N+65) AS String

FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)

EDIT: edited to fix fat finger mistake

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

capn.hector (7/17/2012)
Here is mine, slightly different way to calculate the bits
DECLARE @STR VARCHAR(8000)
SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ID, SUBSTRING(@STR,N,N+55) AS String
FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)

It is a bit simpler but it doesn't produce the desired results from the OP. There is only 2 rows returned and the length seems to be a bit off.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CapnHector SSCoach Points: 16143 More actions · Answer 5

Sean Lange (7/17/2012)
capn.hector (7/17/2012)
Here is mine, slightly different way to calculate the bits
DECLARE @STR VARCHAR(8000)
SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ID, SUBSTRING(@STR,N,N+55) AS String
FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)
It is a bit simpler but it doesn't produce the desired results from the OP. There is only 2 rows returned and the length seems to be a bit off.

i get 3 rows returned and your right on the data being wrong. i had N+55 instead of N+65 as it should be in my posting. stupid fat fingers

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

capn.hector (7/17/2012)
Sean Lange (7/17/2012)
capn.hector (7/17/2012)
Here is mine, slightly different way to calculate the bits
DECLARE @STR VARCHAR(8000)
SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ID, SUBSTRING(@STR,N,N+55) AS String
FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)
It is a bit simpler but it doesn't produce the desired results from the OP. There is only 2 rows returned and the length seems to be a bit off.
i get 3 rows returned and your right on the data being wrong. i had N+55 instead of N+65 as it should be in my posting. stupid fat fingers

OK I changed it to N+65

DECLARE @STR VARCHAR(8000)

SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ID, SUBSTRING(@STR,N,N+65) AS String

FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)

That returns 2 rows for me and the breaks are not in the right spot. It is however a bit simpler to read.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CapnHector SSCoach Points: 16143 More actions · Answer 7

i should not try to solve problems real quick before lunch. empty stomach leads to face palm moments. no need to add N to 65 since thats the data length FACEPALM.

Sean the other reason you may only be getting 2 rows is i use a 0 base tally table (0,1,2) and 0%65 is 0

DECLARE @STR VARCHAR(8000)

SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';

SELECT ROW_NUMBER() OVER (ORDER BY N), SUBSTRING(@STR,N + 1,65)

FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 8

capn.hector (7/17/2012)
i should not try to solve problems real quick before lunch. empty stomach leads to face palm moments. no need to add N to 65 since thats the data length FACEPALM.
Sean the other reason you may only be getting 2 rows is i use a 0 base tally table (0,1,2) and 0%65 is 0
DECLARE @STR VARCHAR(8000)
SET @STR = 'Rang the client and agreed they would begin to pay off £x off their current arrears amount, This will begin next month and continue until arrears are zero';
SELECT ROW_NUMBER() OVER (ORDER BY N), SUBSTRING(@STR,N + 1,65)
FROM Tally WHERE N%65 = 0 AND N < LEN(@STR)

LOL!!!

/facepalm for both of us. Didn't think about the 0 based tally. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CapnHector SSCoach Points: 16143 More actions · Answer 9

not a problem, i use to use a 1 base tally untill i read jeff's new 8k split then started running a 0 based tally. i should probably have included a cteTally with a 0 base but noticed you had all ready posted the tally table link.

EDIT:

Played with it a little and got a slightly simpler version, use where N%65 = 1 and i dont have to add 1 to N and works with a 1 base tally

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ID, SUBSTRING(@STR,N,65) AS String

FROM Tally WHERE N%65 = 1 AND N < LEN(@STR)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

NM...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)