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.
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 aCTEAS(select column1,column2 from Table1),cteXMLAS( select column1 ,CAST('<Categories><categ>' + replace(right(column2,len(column2)-1),'#','</categ><categ>')+'</categ></Categories>' as XML) as Categoriesfrom aCTE)select column1 ,t.u.value('.','varchar(50)') as Category from cteXML cross apply Categories.nodes('Categories/categ') t(u)Veera |
|
|
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. |
|
|
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 urlhttp://dotnetbites.com/split-strings-into-tablethere are many way of writing the sql to split the string to table but the one examined here is the world smallest t-sqlRegards,Alwyn.M |
|
|
Alisa
Starting Member
2 Posts |
Posted - 2014-05-13 : 04:11:57
|
|
|
|
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/ |
|
|
|
|
|
|
|