|
|
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:
| idno | word | n |
| 1 | my | 1 |
| 2 | crazy | 4 |
| 3 | bird | 10 |
Execution of:
EXEC dbo.proc_split_string_to_words 'my,crazy,bird',',';
|
Will bring result like this:
| idno | word | n |
| 1 | ,my, | 1 |
| 2 | ,crazy, | 4 |
| 3 | ,bird, | 10 |
|