split string into columns based on special character

Question

Post reply

split string into columns based on special character

knakka99

Say Hey Kid

Points: 671
More actions
September 21, 2012 at 2:01 pm

#268026

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

venus.pvr SSCarpal Tunnel Points: 4813 More actions · Answer 1

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could use Jeff Moden (& the community) Splitter[/url]

Along with Cross Tabs[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

knakka99 Say Hey Kid Points: 671 More actions · Answer 4

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

knakka99 Say Hey Kid Points: 671 More actions · Answer 5

Luis Cazares

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

knakka99 Say Hey Kid Points: 671 More actions · Answer 6

Sean Lange

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