Hi Simon,

Thanks for the catch.  I don't know why I didn't test it with data with an empty first element in the list! (Doh!) Here's your solution converted over to take advantage of the SQL 2005 ROW_NUMBER() function and CTEs:

Thanks everyone, this has been really useful!

I have used the function to return all strings in the set after the nth string using a little feature for concatenating strings I found on this site...

eg:

DECLARE @strReturn VARCHAR(8000)

SELECT @strReturn = COALESCE(@strReturn,'')+String+' '
FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ')
WHERE Row >= 8

SELECT @strReturn

this will return 'Slow Lazy Dog'

Cheers

David

Neat - I've not seen a select that way before.  Closest I've come is something along the lines of "SELECT dbo.fn(...) FROM <table>"

Only change I'd make is initialize @strReturn first, lest you call COALESCE for every single row from the table when it only applies to the first row.  (Although if NULL comes back then @strReturn will still be null - depends if that is a problem or not.)  Something like:

DECLARE @strReturn VARCHAR(8000)

SET @strReturn = ''

SELECT @strReturn = @strReturn + String + ' '
FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ')
WHERE Row >= 8

SELECT @strReturn

S.

Be careful with that:

SELECT @strReturn = COALESCE(@strReturn,'')+String+' '
FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ')
WHERE Row >= 8

AFAIK, you can't rely on the results being returned in any particular order when doing a concantenation like that from a SELECT statement.  You could conceivably end up with 'Lazy Dog Slow' or 'Dog Lazy Slow' or some other out-of-order result if SQL Server decided it was more efficient to deliver the results of the SELECT in a different order.

James,

Would you mind posting both versions?  I'd like to do a little testing to see what's up... thanks.

I wrote the function in the code below to quickly generate number tables.

It executed this code to load a table with 1,000,000 numbers in 6.780 seconds.  When I ran it to load a table with 10,000,000 numbers, it took about 136 seconds.  100,000 rows took 0.610 seconds, and 10,000 rows took 0.063 seconds.

declare @t datetime
declare @n table( number int )
set @t = getdate()

insert into @n
select
 	number
from
 	-- Function F_TABLE_NUMBER_RANGE available on this link
 	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
 	F_TABLE_NUMBER_RANGE(1,1000000)

select ElapsedTime = getdate()-@t

If you always want the numbers table to start at 1, this demonstrates both an alternative to the function and a comparison of run times from 10 rows to 10 million rows...

--===== Setup the test environment
    SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance & speed

--===== Declare local variables
DECLARE @DesiredRows INT        --Number of rows desired in the result object
DECLARE @StartTime   DATETIME   --For calculation of duration times

--================================================================================
--===== Loop through the tests from 10 to 10 million =============================
    SET @DesiredRows = 10

  WHILE @DesiredRows <= 10000000
  BEGIN

--================================================================================
--      Test the SELECT/INTO Method (temp table)
--================================================================================
--===== If the temporary Tally (Numbers) table exists, drop it
     -- (Included just for test repeatability.  Should not have to do in real life
     -- because won't exist when new connection starts)
     IF OBJECT_ID('TempDB..#Tally','U') IS NOT NULL
        DROP TABLE #Tally

--===== Limit the desired number of rows
    SET ROWCOUNT @DesiredRows

--===== Start the timer and run the test
    SET @StartTime = GETDATE()
 SELECT IDENTITY(INT,1,1) AS N
   INTO #Tally
   FROM Master.dbo.SysColumns sc1 WITH (NOLOCK),
        Master.dbo.SysColumns sc2 WITH (NOLOCK)

--===== Report the rowcount and duration in seconds
  PRINT STR(@@ROWCOUNT) + ' Rows Inserted '
      + CONVERT(CHAR(13),GETDATE()-@StartTime,114) + ' Duration SELECT/INTO Method'

--===== Return to normal unlimited rowcounts
    SET ROWCOUNT 0

--================================================================================
--      Test the F_TABLE_NUMBER_RANGE Method (table variables)
--      Function F_TABLE_NUMBER_RANGE available on this link  
--      http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685  
--================================================================================

--===== Start the timer and run the test
    SET @StartTime = GETDATE()
DECLARE @N TABLE(Number INT) --Not sure why this works in a loop, but it does
 INSERT INTO @N
 SELECT Number
   FROM dbo.F_TABLE_NUMBER_RANGE(1,@DesiredRows)

--===== Report the rowcount and duration in seconds
  PRINT STR(@@ROWCOUNT) + ' Rows Inserted '
      + CONVERT(CHAR(13),GETDATE()-@StartTime,114) + ' Duration F_TABLE_NUMBER_RANGE Method'

--===== Delete rows from the table variable so as not to build up rows between tests
--TRUNCATE TABLE @N --Doesn't work on table variables...
--DROP TABLE @N     --Neither does this...
DELETE @N           --But this does.

  PRINT REPLICATE('=',78)

--================================================================================
--===== End of test loop =========================================================
    SET @DesiredRows = @DesiredRows * 10
    END

... and here's the results it produced on my humble 1.8 Ghz 1 GB Ram SQL Server 2000 SP 4 Developer's Edition desktop box at home...

        10 Rows Inserted 00:00:00:000  Duration SELECT/INTO Method
        10 Rows Inserted 00:00:00:000  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================
       100 Rows Inserted 00:00:00:000  Duration SELECT/INTO Method
       100 Rows Inserted 00:00:00:013  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================
      1000 Rows Inserted 00:00:00:000  Duration SELECT/INTO Method
      1000 Rows Inserted 00:00:00:017  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================
     10000 Rows Inserted 00:00:00:033  Duration SELECT/INTO Method
     10000 Rows Inserted 00:00:00:077  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================
    100000 Rows Inserted 00:00:00:203  Duration SELECT/INTO Method
    100000 Rows Inserted 00:00:00:750  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================
   1000000 Rows Inserted 00:00:02:000  Duration SELECT/INTO Method
   1000000 Rows Inserted 00:00:08:093  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================
  10000000 Rows Inserted 00:00:20:253  Duration SELECT/INTO Method
  10000000 Rows Inserted 00:01:48:123  Duration F_TABLE_NUMBER_RANGE Method
==============================================================================