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

split string into columns based on special character Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 2:01 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:37 AM
Points: 49, Visits: 116
/*The Displayname column has to be split into 5 columns basing on the '/' .
I have noticed that the Field4 and Field5 are always null. */
/*This is how the output table should be. (also attached screenshot and sql to create the table)
But the issue is that there is no consistency with regards to the '/'*/

CREATE TABLE TEMP#(
Displayname varchar(35),
FIELD1 varchar(35),
FIELD2 varchar(35),
FIELD3 varchar(35),
FIELD4 varchar(35),
FIELD5 varchar(35)
)
INSERT INTO TEMP#
select '1071 E MAIN STREET////'as Displayname,'1071 E MAIN STREET'as FIELD1,''as FIELD2,''as FIELD3,''as FIELD4,''as FIELD5
union
select '/1/REC','','1','REC','',''
union
select 'Recovery/1/REC','Recovery','1','REC','',''
union
select '//3','','','3','',''
union
select 'Bldg 3150//327//','Bldg 3150','','327','',''
union
select 'nk','nk','','','',''
union
select ' CSS////','CSS','','','',''


SELECT * FROM TEMP#


  Post Attachments 
string break.JPG (5 views, 24.13 KB)
Post #1362967
Posted Friday, September 21, 2012 2:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:21 PM
Points: 120, Visits: 445
Use the below code:

SELECT
ISNULL(x.y.value('Display[1]', 'VARCHAR(30)'),'') AS col1,
ISNULL(x.y.value('Display[2]', 'VARCHAR(30)'),'') AS col2,
ISNULL(x.y.value('Display[3]', 'VARCHAR(30)'),'') AS col2,
ISNULL(x.y.value('Display[4]', 'VARCHAR(30)'),'') AS col2,
ISNULL(x.y.value('Display[5]', 'VARCHAR(30)'),'') AS col2
FROM
(SELECT CAST('<aa><Display>'+REPLACE(DisplayName,'/','</Display><Display>')+'</Display></aa>' AS XML) as Display FROM TEMP# )cte
CROSS APPLY cte.Display.nodes('aa') x(y)

Praveena
Post #1362988
Posted Friday, September 21, 2012 2:41 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 @ 5:19 PM
Points: 3,374, Visits: 7,303
You could use Jeff Moden (& the community) Splitter
Along with Cross Tabs



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1362993
Posted Friday, September 21, 2012 3:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
Luis is absolutely correct. Use the delimited splitter he linked and then a cross tab.

BTW, nice job posting ddl and sample data. You are new around here but obviously understand the importance of that. It goes a long way with the people around here.

One thing is that the table you posted is a persistent table. Temp tables START with a pound sign. Your code will create a permanent table called TEMP#. To make it a temp table it should have been #TEMP.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363019
Posted Monday, September 24, 2012 8:42 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:37 AM
Points: 49, Visits: 116
Thank you Praveena this did the job.

Only it gave an exception if there was a "&" character in the cell, however they were only 5 records like this which I can enter manually.(but just wanted to know if there was a reason or alternative..

Post #1363539
Posted Monday, September 24, 2012 8:45 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:37 AM
Points: 49, Visits: 116
Luis Cazares
I did go through the link and funtion (DelimitedSplit8k). learned for more things. Thank you
Post #1363541
Posted Monday, September 24, 2012 8:49 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:37 AM
Points: 49, Visits: 116
Sean Lange
thank you for correcting me (#Temp) I appreciate it.
Post #1363547
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse