Here is what may be considered a better example of using a Tally table. It is dynamically created with the function. And FYI, it is formatted the way I like to format my code. I've heard some complain that it makes my code look more complex.

USE [SandBox]

GO

/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 07/02/2010 17:28:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE function [dbo].[DelimitedSplit] (

@pString varchar(max),

@pDelimiter char(1)

)

returns table

as

return

with

a1 as (select 1 as N union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

--a4 as (select

-- 1 as N

-- from

-- a3 as a

-- cross join a2 as b),

Tally as (select top (len(@pString))

row_number() over (order by a.N) as N

from

a3 as a

cross join a2 as b),

ItemSplit(

ItemOrder,

Item

) as (

SELECT

N,

SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)

FROM

Tally

WHERE

N < LEN(@pDelimiter + @pString + @pDelimiter)

AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter

)

select

row_number() over (order by ItemOrder) as ItemID,

Item

from

ItemSplit

GO

declare @Text varchar(max),

@StringDelimiter char(1);

SET @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.?';

SET @StringDelimiter = '.';

select * from dbo.DelimitedSplit (@Text, @StringDelimiter);