Looping through the input string either one character at a time or jumping
from space to space using charindex would be the common way to go.

I would be interested in creating a Split function that built a temp table
instead of an array. Then you could use the LEN function to easily find
and/or sort the individual words.

Usage of below function:

SELECT * FROM dbo.Split('In the course of human events', ' ')

This could get the longest word
SELECT TOP 1 value FROM dbo.Split('In the course of human events', ' ')
ORDER BY length desc

CREATE   FUNCTION dbo.Split(@str varchar(8000), @delimiter varchar(20) = '
')

RETURNS @T TABLE

( position int IDENTITY PRIMARY KEY,
 value varchar(8000) ,
 length smallint null
)

AS

BEGIN
DECLARE @i int
SET @i = -1
WHILE (LEN(@str) > 0)
  BEGIN
    SET @i = CHARINDEX(@delimiter , @str)
    IF (@i = 0) AND (LEN(@str) > 0)
      BEGIN
        INSERT INTO @T (value, length) VALUES (@str, LEN(@str))
          BREAK
      END
    IF (@i > 1)
      BEGIN
        INSERT INTO @T (value, length) VALUES (LEFT(@str, @i - 1),
LEN(LEFT(@str, @i - 1)))
        SET @str = RIGHT(@str, (LEN(@str) - @i))
      END
    ELSE
      SET @str = RIGHT(@str, (LEN(@str) - @i))
    END
RETURN
END

Aaron,

There is a simple set based solution:

DECLARE @c VARCHAR(70)
SET @c = ' 123 12345 1234567 123456 1234567 123 1234 '
SELECT TOP 1 SUBSTRING(@c, NotBlank, NextBlank)
FROM (
SELECT n.Number NotBlank, CHARINDEX(' ', @c, n.Number + 1) - n.Number
NextBlank FROM dbo.Numbers n
WHERE SUBSTRING(@c, n.Number, 1) <> ' '
) t
ORDER BY NextBlank DESC

----------------------------------------------------------------------
1234567

(1 row(s) affected)

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/