Expanding/Splitting Rows of a table into more rows

Question

I need to create a table-valued function, with just one col, that returns the multi-valued data in a table as single values

For example, this is my table:

And, this is what the table-valued function would return:

I've already read this post, but it seems unnecessarily complex .

There are probably several hundred duplicate questions on this, but I am not sure any answer as completely as the following 3 articles - [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up), and [Splitting Strings : Now with less T-SQL](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) — GarethD, Nov 03 '17 at 13:14
Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/q/5493510/1048425) - although the accepted answers is not the best way (in my opinion) - I would recommend reading [this answer](https://stackoverflow.com/a/36305493/1048425) — GarethD, Nov 03 '17 at 13:17

score 3 · Accepted Answer · answered Nov 03 '17 at 13:04

3

However, you could achieve the above without using Function you could use XML method :

SELECT split.a.value('.', 'VARCHAR(MAX)') [TestCol]
FROM
(
    SELECT CAST('<M>'+REPLACE([TestCol], '#', '</M><M>')+'</M>' AS XML) AS String
    FROM [Table]
) A
CROSS APPLY String.nodes('/M') AS split(a);

answered Nov 03 '17 at 13:04

Yogesh Sharma

49,870
5
26
52

just brilliant ! – LearnByReading Nov 03 '17 at 13:40
why is this case sensitive? If you try to capitalize words "nodes" or "value", it won't compile, why? Isn't SQL case-insensitive? – LearnByReading Nov 06 '17 at 14:52
1

@LearnByReading In `Sql Server` XQuery statements are case sensitive since xml (i.e. value() method & nodes() method) is case sensitive – Yogesh Sharma Nov 07 '17 at 04:13

score 2 · Answer 2 · answered Nov 06 '17 at 03:48

If performance is important the fastest T-SQL "splitter" for varchar(8000)/nvarchar(4000) is going to be DelimitedSplit8K_LEAD by Eirikur Eiriksson. Link to the function at the end of the article.

Example

-- sample data
declare @table table (someid int identity, somestring varchar(100));
insert @table (somestring) values ('abc#123##xxx'),('ff#rrr#ddd');
-- example
select someid, ItemNumber, Item 
from @table t
cross apply dbo.DelimitedSplit8K_LEAD(t.somestring, '#');

Results

someid      ItemNumber    Item
----------- ------------- -----
1           1             abc
1           2             123
1           3             
1           4             xxx
2           1             ff
2           2             rrr
2           3             ddd

Expanding/Splitting Rows of a table into more rows

2 Answers2