Where each space-delimited “word” in the string appears in the table along with the number of times it appeared in the input string

CREATE FUNCTION split (@string VARCHAR(MAX))

RETURNS
 @returnList TABLE (
   word varchar(MAX),
   count int
 )
AS

BEGIN

 DECLARE @word NVARCHAR(255)
 DECLARE @pos INT
 DECLARE @length INT

 WHILE CHARINDEX(' ', @string) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(' ', @string)  
  SELECT @word = SUBSTRING(@string, 1, @pos-1)
  SELECT @length = LEN(@word)

  IF (@length > 0)
    INSERT INTO @returnList 
    SELECT @word, @length

  SELECT @string = SUBSTRING(@string, @pos+1, LEN(@string)-@pos)
 END
 
IF (LEN(@string) > 0)
   INSERT INTO @returnList
   SELECT @string, LEN(@string)

 RETURN
END