?
Solved

Split column into rows based on new line

Posted on 2013-12-12
8
Medium Priority
?
287 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
  • 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 

Author Comment

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

Accepted Solution

by:
Angelp1ay earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

593 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