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
