split string into columns based on special character

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » split string into columns based on special...

split string into columns based on special character

Rate Topic

Display Mode

Topic Options

Author

Message

knakka 14475

Posted Friday, September 21, 2012 2:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 13, 2013 1:35 PM
Points: 38, Visits: 83

/*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

venus.pvr

Posted Friday, September 21, 2012 2:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:52 AM
Points: 109, Visits: 396

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

Luis Cazares

Posted Friday, September 21, 2012 2:41 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:26 PM
Points: 958, Visits: 1,917

You could use Jeff Moden (& the community) Splitter
Along with Cross Tabs

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1362993

Sean Lange

Posted Friday, September 21, 2012 3:31 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:18 PM
Points: 8,562, Visits: 8,215

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. BigGrin

_______________________________________________________________

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

Post #1363019

knakka 14475

Posted Monday, September 24, 2012 8:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 13, 2013 1:35 PM
Points: 38, Visits: 83

Thank you Praveena this did the job. Smile

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

knakka 14475

Posted Monday, September 24, 2012 8:45 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 13, 2013 1:35 PM
Points: 38, Visits: 83

Luis Cazares
I did go through the link and funtion (DelimitedSplit8k). learned for more things. Thank you

Post #1363541

knakka 14475

Posted Monday, September 24, 2012 8:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 13, 2013 1:35 PM
Points: 38, Visits: 83

Sean Lange
BigGrin

thank you for correcting me (#Temp) I appreciate it.

Post #1363547

« Prev Topic | Next Topic »

Permissions