TSQL -> Procedure that splits character string to words

Following procedure splits sentence to words recieving separator character as parameter. Result dataset (words) returned in select. (Syntax for SQL Server 2008 and higher).


IF EXISTS (SELECT * FROM sys.objects
  WHERE name = 'proc_split_string_to_words' AND type = 'P'
DROP PROCEDURE [dbo].[proc_split_string_to_words]
GO

/* ===================================================================
  Author: [ amper ]
  Create date: [ 2011-03-21 ]
  Description: [this procedure recieves text as input + separator
  char, split text to words and returns list of words in select]
===================================================================== */

CREATE PROCEDURE proc_split_string_to_words
  @inword NVarchar(60) = '', @split NVarchar(1) = ' '
AS
BEGIN

  DECLARE @tmp_word NVarchar(60);

  DECLARE @tmp_parse_text TABLE
  (idno TINYINT, text_str NVarchar(60), n TINYINT);
  DECLARE @tmp_NameTable TABLE
  (Name NVarchar(60));
  DECLARE @tmp_WordsTable TABLE
  (idno TINYINT, word NVarchar(60), n TINYINT);

  -- trim blanks
  SET @tmp_word = RTRIM(LTRIM(@inword));

  IF (CHARINDEX(@split,@tmp_word)=0)
  BEGIN
   -- unsplitable string
   INSERT INTO @tmp_WordsTable (idno, word, n)
   VALUES (1, @tmp_word, 0);

  END
  ELSE
  BEGIN

   INSERT INTO @tmp_NameTable (Name)
   VALUES (@split + @tmp_word + @split);

   -- split input value to words

   INSERT INTO @tmp_parse_text (idno, text_str, n)
   SELECT ROW_NUMBER() OVER (ORDER BY b.n) AS idno,
   @split + a.name + @split AS text_str, b.n
   FROM @tmp_NameTable AS a, num_seq AS b
   WHERE SUBSTRING(a.name, b.n, 1) = @split AND b.n <= LEN(a.name)+1
   ORDER BY b.n;

   INSERT INTO @tmp_WordsTable (idno, word, n)
   SELECT a.idno, SUBSTRING(a.text_str, a.n + 1, (b.n+1)-a.n), a.n
   FROM @tmp_parse_text AS a INNER JOIN @tmp_parse_text AS b
   ON (a.idno=(b.idno-1))
   ORDER BY a.idno, a.n;

  END

  -- return result
  SELECT * FROM @tmp_WordsTable;
END
GO




Execution of:
EXEC dbo.proc_split_string_to_words 'my crazy bird',' ';

Will bring result like this:
idnowordn
1my1
2crazy4
3bird10

Execution of:
EXEC dbo.proc_split_string_to_words 'my,crazy,bird',',';

Will bring result like this:
idnowordn
1,my,1
2,crazy,4
3,bird,10



sqlexamples.info