Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Split string into multiple rows

Author  Topic 

dzabor
Posting Yak Master

138 Posts

Posted - 2014-04-14 : 17:04:31
How can I pull words out of a column and return as a row?

column1 column2

12345 the#quick#brown#fox

12346 mmm#turkey


-result set

column1 column2

12345 the

12345 quick

12345 brown

12345 fox

12346 mmm

12346 turkey

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-15 : 00:54:21
CREATE TABLE Table1 (column1 INT,column2 NVARCHAR(MAX))
INSERT INTO Table1 VALUES(12345,'the#quick#brown#fox'),(12346,'mmm#turkey')

;WITH aCTE
AS
(select column1,column2 from Table1)
,cteXML
AS
(
select
column1
,CAST('<Categories><categ>' + replace(right(column2,len(column2)-1),'#','</categ><categ>')+'</categ></Categories>' as XML) as Categories
from aCTE
)
select
column1
,t.u.value('.','varchar(50)') as Category
from cteXML
cross apply
Categories.nodes('Categories/categ') t(u)

Veera
Go to Top of Page

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-16 : 15:23:31
You can also create a split function and use a delimiter as required, which is # in your case. Look at this article in which I wrote about using a split function. http://sqlsaga.com/sql-server/split-function-in-sql-server/



Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

adsingh82
Starting Member

20 Posts

Posted - 2014-05-13 : 02:13:22
world smallest t-sql to split the string into table is in the below url

http://dotnetbites.com/split-strings-into-table

there are many way of writing the sql to split the string to table but the one examined here is the world smallest t-sql

Regards,
Alwyn.M
Go to Top of Page

Alisa
Starting Member

2 Posts

Posted - 2014-05-13 : 04:11:57
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-13 : 15:13:52
If you happen to be curious about Split function performance you might want to take a look at this article, plus it has one of the fastest SQL split functions available:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page
   

- Advertisement -