December 11, 2013 at 10:22 am
I have seen posts to split column into multiple records based on delimited value.
I need to split a vchar(4000) column into multiple records into column vchar(80)
based on substring if not blank
I can use the below code...would be okay if original column was only vchar(400)
but obviously there has to be a better way to loop thru.
Thanks for the help in advance.
SELECT cust, SUBSTRING(wptext, 1, 80) AS wptext, 1 as seq
FROM TableA WHERE SUBSTRING(wptext, 1, 80) <> ' '
UNION ALL
SELECT cust, SUBSTRING(wptext, 81, 80) AS wptext, 2 as seq
FROM TableA WHERE SUBSTRING(wptext, 81, 80) <> ' '
UNION ALL
SELECT cust, SUBSTRING(wptext, 161, 80) AS wptext, 3 as seq
FROM TableA WHERE SUBSTRING(wptext, 161, 80) <> ' '
UNION ALL
SELECT cust, SUBSTRING(wptext, 241, 80) AS wptext, 4 as seq
FROM TableA WHERE SUBSTRING(wptext, 241, 80) <> ' '
UNION ALL
SELECT cust, SUBSTRING(wptext, 321, 80) AS wptext, 5 as seq
FROM TableA WHERE SUBSTRING(wptext, 321, 80) <> ' '
order by cust, seq
December 11, 2013 at 10:42 am
A Tally table (or in this case a cteTally) can come to the rescue.
Check the following code and ask for anything that you don't understand.
WITH E1(n) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
cteTally(n) AS(
SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))
FROM E1, E1 b
)
SELECT cust, SUBSTRING(wptext, (80 * (n-1)) + 1, 80 ) AS wptext, n as seq
FROM TableA a
JOIN cteTally t ON t.n <= CEILING( LEN( a.wptext) / 80.0)
order by cust, n
December 11, 2013 at 11:06 am
SELECT x.*
FROM TableA
CROSS APPLY (
SELECT cust, SUBSTRING(wptext, 1, 80), 1 UNION ALL
SELECT cust, SUBSTRING(wptext, 81, 80), 2 UNION ALL
SELECT cust, SUBSTRING(wptext, 161, 80), 3 UNION ALL
SELECT cust, SUBSTRING(wptext, 241, 80), 4 UNION ALL
SELECT cust, SUBSTRING(wptext, 321, 80), 5
) x (cust, wptext, seq)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2013 at 12:16 pm
The Tally Table appears to be what I am looking to do.
I will verify some of the results but initial return values look okay.
So the code has several "SELECT 1 UNION ALL"...how do you know how many to use?
The code also uses "SELECT TOP 50 ROW_NUMBER()"....how do you know to use 50?
Thanks again for the help!
December 11, 2013 at 12:23 pm
on side note....how do you post the script inside the window box. I read best practice for posting but not seeing it.
Do you use the IFCode Shortcuts...still not quite sure how those work either.
December 11, 2013 at 12:30 pm
All the "SELECT 1 UNION ALL" create 10 rows which I later use to create 100 rows with a CROSS JOIN in the next CTE.
I'm using TOP 50 because you said that you have a varchar(4000) and 4000/80 = 50.
You can change the value to any value you need or just remove it.
To put your code in the code box you use the IFCode Shortcuts. Either you click on the [ code="sql"] one before inserting the code and then put the code between the labels or you can select the code and click on the shortcut to put the labels around your selection.
December 12, 2013 at 10:07 am
So I was able to split column based on specific substring length and export the data to my target source; at which point I noticed data contained Line Feed.
So is there a way to split column into multiple records based on Line Feed and excluding Line Feed char(10)
Thanks again in advance for your help.
December 12, 2013 at 10:13 am
I need you to post sample data and expected results, because I'm not sure that I understand the problem. To know how to do it, please read the article linked on my signature.
December 12, 2013 at 10:34 am
Richard Cranston (12/12/2013)
So I was able to split column based on specific substring length and export the data to my target source; at which point I noticed data contained Line Feed.So is there a way to split column into multiple records based on Line Feed and excluding Line Feed char(10)
Thanks again in advance for your help.
See Jeff Moden's splitter at http://www.sqlservercentral.com/articles/Tally+Table/72993/
and use CHAR(10) as the delimiter
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2013 at 10:45 am
CREATE TABLE [dbo].[tableA](
[CUST] [varchar](9) NOT NULL,
[WPTEXT] [varchar](4000) NULL)
INSERT INTO tableA values ('12345' ,
'6/26/08 BAD DEBT 1,351.28 BANKRUPT C-11' + CHAR(10) +
'07/03/08 BAD DEBT RECOVERY 273.07 BAL OWED 1,078.21.' + CHAR(10) +
'09/17/09 BAD DEBT RECOVERY 462.07 BAL OWED 616.14.' + CHAR(10) +
'06/29/09 OVER PAYMENTS USED AGAINST BAD DEBT. (196.81)' + CHAR(10) +
'BALANCE OWED 419.33.' + CHAR(10) +
'11/30/09 BAD DEBT RECOVERY 1.70 BAL OWED 417.63.' + CHAR(10) +
'12/23/10-BAD DEBT RECOVERY 10.20 BAL OWED 407.43.')
SELECT cust, wptext, charindex(char(10), wptext) as firstposlinefeed
FROM tableA where charindex(char(10), wptext) <> 0
Hope this code is helpful
December 12, 2013 at 1:44 pm
I was able to use Jeff Moden's splitter at http://www.sqlservercentral.com/articles/Tally+Table/72993/
and use CHAR(10) as the delimiter, and the results are not all bunched up; but I can still end up with a
split over 70 chars long...so I really need to combine both....Thanks to everyone's help!
December 12, 2013 at 2:03 pm
It's easy to combine both techniques. Here's an example returning rows with 4 characters or less.
WITH E1(n) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
cteTally(n) AS(
SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))
FROM E1, E1 b
)
SELECT cust,
SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ),
LEN(s.Item),
LEN(SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ))
FROM tableA a
CROSS APPLY dbo.DelimitedSplit8K( wptext, CHAR(10)) s
JOIN cteTally t ON t.n <= CEILING( LEN( s.Item) / 40.0)
ORDER BY ItemNumber, n
December 12, 2013 at 2:17 pm
Thanks Luis for the posting of both combinations.....that is great!! Just what I needed.
Thanks for Jeff Modine splitter.
I am always learning new things!!
December 16, 2013 at 1:45 pm
I need to generate the Sequence Number from both combined data; if I use "n" value from the order by,
then I get for example: 1,2,3,4 from split data and then 1,2 from the column length 40 data (total 6 records for a specific cust).
I need to get 1,2,3,4,5,6 instead of 1,2,3,4,1,2.
WITH E1(n) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
cteTally(n) AS(
SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))
FROM E1, E1 b
)
SELECT cust,
SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ),
n as Seq, <---- I tried adding this but not correct
LEN(s.Item),
LEN(SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ))
FROM tableA a
CROSS APPLY dbo.DelimitedSplit8K( wptext, CHAR(10)) s
JOIN cteTally t ON t.n <= CEILING( LEN( s.Item) / 40.0)
ORDER BY ItemNumber, n
December 16, 2013 at 2:17 pm
You need to add an additional ROW_NUMBER() instead of the previously generated n.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply