?
Solved

Split column into rows based on new line

Posted on 2013-12-12
8
Medium Priority
?
282 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

777 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