set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTERFUNCTION[dbo].[JoeySplitWords](@textnvarchar(4000)) RETURNS@wordsTABLE (word nvarchar(4000)) AS BEGIN DECLARE@wordBeginsmallint, @wordEndsmallint, @wordnvarchar(4000) SELECT@wordBegin=1 WHILE@wordBegin<=LEN(@text) BEGIN SELECT@wordEnd=CHARINDEX(',', @text, @wordBegin) -1 IF(@wordEnd-@wordBegin<-1)-- not found BEGIN SELECT@word=LTRIM(RTRIM(SUBSTRING(@text, @wordBegin, LEN(@text) -@wordBegin+1))) INSERTINTO@words(word) VALUES(@word) BREAK END ELSEIF(@wordEnd-@wordBegin=-1)-- found ',' BEGIN SELECT@wordBegin=@wordEnd+2 END ELSE-- found BEGIN SELECT@word=LTRIM(RTRIM(SUBSTRING(@text, @wordBegin, @wordEnd-@wordBegin+1))) INSERTINTO@words(word) VALUES(@word) SELECT@wordBegin=@wordEnd+2 END END RETURN END