Jeff Moden (7/2/2010)
BWAA-HAA!!!! First, this is NOT a good example of Tally Table code. It doesn't use a Tally Table and it doesn't use anything that could be called efficient. Anyone who uses a recursive CTE to generate Tally numbers just doesn't know what hidden RBAR is. 😛I agree with what someone else has stated... this code should be used as an example of how NOT to write code and how NOT to accomplish a split. Same goes for any example in this thread that uses a recursive CTE to do the split. 😉
I hope no one actually copies the code to use as a split function but I will say it was very interesting in how many "extra" things were added to the code in an attempt at obfuscation. Heh... maybe it should be titled "job security methods". :hehe:
The urge to submit a post was burning me for a couple of days, but I decided to wait, because I was afraid that I will not be able to find the way to type what I think about the script in QotD without sounding rude, so I am glad that Jeff, Hugo and vk-kirov stood up to it. I agree that this is one of the finest examples how NOT TO DO something. There are some valid reasons to use recursive CTE, for example to write BOM queries in 2005 (HierarchyID is not yet available), but these reasons do not extend to creating a tally table, specifically with @text declared as varchar(500) but the recursion level restricted to 32767 (???).
with tally (number) as
(
select
top (len(@text))
row_number() over (order by [object_id]) number
from sys.objects
)
should do the trick right?
Using xml to split delimited values is relatively cheap and not difficult to understand (Does not include the job security though :hehe:). Here is one example based on simply replacing the delimiter with closing and opening the node of arbitrary name and appending opening node to the head and closing to the tail:
use AdventureWorks;
go
declare @delimiter char(1);
declare @text nvarchar(500);
declare @xml xml;
-- set variable values
select
@delimiter = '.',
@text = 'This t-sql will split these sentences into rows.' +
'How many rows will be returned?.' + 'M.a.y.b.e..n.o.n.e.?',
@xml = '<r>' + replace(@text, @delimiter, '</r><r>') + '</r>';
-- here is the "split" in all its glory
select
item.value('text()[1]', 'varchar(100)') single_item
from @xml.nodes('//r') R(item);
The above happily returns desired output:
single_item
-----------------------------------------------
This t-sql will split these sentences into rows
How many rows will be returned?
M
a
y
b
e
NULL
n
o
n
e
?
Oleg