Solved

Split column into rows based on new line

Posted on 2013-12-12
8
279 Views
Last Modified: 2013-12-13
I have a varchar field where the text is like this:

Class 7:    Lorem ipsum dolor sit amet, consectetur adipisicing
Class 9:    At vero eos et accusamus et iusto odio dignissimos
Class 10:  Temporibus autem quibusdam et aut officiis debitis

I need to make 2 fields from the the text, and many rows depending on how many lines there is, so my new table should look like this:

Class     Text
7           Lorem ipsum dolor sit amet, consectetur adipisicing
9           At vero eos et accusamus et iusto odio dignissimos
10         Temporibus autem quibusdam et aut officiis debitis

Is this possible to do?
0
Comment
Question by:gosi75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39714341
SELECT Class = CONVERT(int, LEFT(REPLACE(your_column, 'Class ', ''), CHARINDEX (':', your_column)-7)),
	Text = RTRIM(LTRIM(SUBSTRING(your_column, CHARINDEX (':', your_column)+1, len(your_column))))
FROM your_table

Open in new window

0
 

Author Comment

by:gosi75
ID: 39714521
Hi,
Thank you for the reply. But this only shows me one row where the class number for the first item is in the class column. Maybe I wasn't clear enough, but what I need as an output is three rows where the Class is 7,9 and 10. In the Text column the text that belongs to each class sholud be in three different rows, that is:


                  Class         Text
Row 1         7               Lorem ipsum dolor sit amet, consectetur adipisicing
Row 2         9               At vero eos et accusamus et iusto odio dignissimos
Row 3         10             Temporibus autem quibusdam et aut officiis debitis
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39714833
Ahhh I misunderstood. The entire input is in a single cell?

Will have another crack at it.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:gosi75
ID: 39714841
Yes, it's in a single cell
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 39715330
Yea... that was a much harder puzzle! Forced me to learn more about the SQL XML data type and CROSS APPLY.

This should work :)
WITH CteXml AS (
    SELECT parsed = CAST(N'<H><r>' + REPLACE(REPLACE(<<your_column>>,char(13),'</r><r>'),char(10),'') + '</r></H>' AS XML) FROM <<your_table>>
),
CteLine AS (
	SELECT line = Lines.l.value('.', 'NVARCHAR(MAX)')
	FROM   CteXml
	CROSS APPLY parsed.nodes('/H/r') Lines(l)
)

SELECT Class = CONVERT(int, (LEFT(REPLACE(line, 'Class ', ''), CHARINDEX (':', line)-7))),
	Text = RTRIM(LTRIM(SUBSTRING(line, CHARINDEX (':', line)+1, len(line))))
FROM CteLine

Open in new window

0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39715339
...so let me also explain a bit.

The first CTE takes your input text and converts it to some ad hoc XML, the result being roughly this (but without line breaks - I just put some in to make it readable):
<H>
    <r>Class 7:    Lorem ipsum dolor sit amet, consectetur adipisicing</r>
    <r>Class 9:    At vero eos et accusamus et iusto odio dignissimos</r>
    <r>Class 10:  Temporibus autem quibusdam et aut officiis debitis</r>
</H>

Open in new window


The second CTE uses the XML nodes function to convert that XML into a table - nodes is a table valued function. It then CROSS APPLYs it to the original data (essentially it's like a join but the right side of the join is dynamic based on the left - in this case the function nodes applied to the XML field on the left). This gets you this:
        Line
Row 1 | Class 7:    Lorem ipsum dolor sit amet, consectetur adipisicing
Row 2 | Class 9:    At vero eos et accusamus et iusto odio dignissimos
Row 3 | Class 10:  Temporibus autem quibusdam et aut officiis debitis 

Open in new window


The final part of the query is just what I had written previously which splits each line to get the number and the text as 2 columns.
0
 

Author Closing Comment

by:gosi75
ID: 39716389
Excellent job, thank you very much for this :)
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39716562
You're welcome. It was a great learning experience for me too :)
Have a nice day!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question