Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Split column into rows based on new line

Posted on 2013-12-12
8
Medium Priority
?
283 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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