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: Thursday, August 21, 2014 9:13 AM
Points: 146, Visits: 142
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 @ 8:15 PM
Points: 3,564, Visits: 7,702
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Thursday, August 21, 2014 9:13 AM
Points: 146, Visits: 142
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: Thursday, August 21, 2014 9:13 AM
Points: 146, Visits: 142
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 @ 8:15 PM
Points: 3,564, Visits: 7,702
You need to add an additional ROW_NUMBER() instead of the previously generated n.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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