Split Words

A table valued function that breaks a block of text into individual words, splitting on spaces and the punctuation characters , : / -. It also remembers each word's position in the original text, which makes it useful for building simple full-text style search over plain columns. Common filler words (an, the, of, and, &) are stripped out so they do not pollute your search terms.

Example: SELECT * FROM dbo.SplitWords('The quick brown fox jumps over the lazy dog')

CREATE FUNCTION SplitWords(@text nvarchar(max)) 
   RETURNS @words TABLE (
      pos smallint, 
      value nvarchar(max)
   ) 
AS 
BEGIN 
   SET @text = RTRIM(@text)
 
   DECLARE @delims nvarchar(10) 
   SET @delims = ' ,:/-'
   DECLARE @pos smallint, @i smallint, @s nvarchar(max)
 
   SET @pos = 1 
 
   WHILE @pos < LEN(@text) 
      AND CHARINDEX(SUBSTRING(@text, @pos, 1), @delims) > 0 
      SET @pos = @pos + 1 
 
   WHILE @pos <= LEN(@text) 
   BEGIN 
      SET @i = PATINDEX('%[' + @delims + ']%', 
         SUBSTRING(@text, @pos, len(@text) - @pos + 1)) 
      IF @i > 0 
      BEGIN 
         SET @i = @i + @pos - 1 
         IF @i > @pos 
         BEGIN 
            -- @i now holds the earliest delimiter in the string
            SET @s = SUBSTRING(@text, @pos, @i - @pos)
            INSERT INTO @words 
            VALUES (@pos, @s)
         END 
         SET @pos = @i + 1 
 
         WHILE @pos < LEN(@text) 
            AND CHARINDEX(SUBSTRING(@text, @pos, 1), @delims) > 0 
            SET @pos = @pos + 1 
      END
      ELSE 
      BEGIN
         SET @s = SUBSTRING(@text, @pos, LEN(@text) - @pos + 1)
         INSERT INTO @words 
         VALUES (@pos, @s) SET @pos = LEN(@text) + 1
      END 
   END
   -- remove common words that we don't want to search for 
   DELETE FROM @words 
   WHERE value IN ('an', 'the', 'of', 'and', '&') 
   RETURN 
END