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