July 17, 2012 at 8:45 am
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
July 17, 2012 at 9:50 am
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/
July 17, 2012 at 10:14 am
Many thanks for your help - I shall read and hopefully digest!
July 17, 2012 at 11:09 am
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 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]
July 17, 2012 at 11:15 am
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/
July 17, 2012 at 12:29 pm
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 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]
July 17, 2012 at 12:33 pm
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/
July 17, 2012 at 12:53 pm
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 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]
July 17, 2012 at 1:15 pm
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/
July 17, 2012 at 1:54 pm
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 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]
July 18, 2012 at 1:35 am
NM...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply