Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

split column into multiple records based on substring fixed lenght Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 1:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:12 PM
Points: 146, Visits: 141
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!



Post #1522470
Posted Thursday, December 12, 2013 2:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,336, Visits: 7,199
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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1522472
Posted Thursday, December 12, 2013 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:12 PM
Points: 146, Visits: 141
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!!



Post #1522478
Posted Monday, December 16, 2013 1:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:12 PM
Points: 146, Visits: 141
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






Post #1523420
Posted Monday, December 16, 2013 2:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,336, Visits: 7,199
You need to add an additional ROW_NUMBER() instead of the previously generated n.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1523431
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse