GETALLWORDS Inserts The Words From A String Into T

-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
-- GETALLWORDS(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value table
-- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- Example
-- declare @cString nvarchar(4000)
-- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
-- select * from dbo.GETALLWORDS(@cString, default)
-- select * from dbo.GETALLWORDS(@cString, ' ,.')
-- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions
CREATE function GETALLWORDS (@cSrting nvarchar(4000), @cDelimiters nvarchar(256))
returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)
begin
-- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit

select @k = 1, @wordcount = 0, @nEndString = 1 + datalength(@cSrting) /(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

while charindex(substring(@cSrting, @k, 1), @cDelimiters) > 0 and @nEndString > @k -- skip opening break characters, if any
set @k = @k + 1

if @k < @nEndString
begin
select @wordcount = 1, @BegOfWord = @k, @flag = 1 -- count the one we are in now count transitions from 'not in word' to 'in word'
-- if the current character is a break char, but the next one is not, we have entered a new word
while @k < @nEndString
begin
if @k +1 < @nEndString and charindex(substring(@cSrting, @k, 1), @cDelimiters) > 0
begin
if @flag = 1 and charindex(substring(@cSrting, @k-1, 1), @cDelimiters) = 0
begin
select @flag = 0
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cSrting, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word
end
if charindex(substring(@cSrting, @k+1, 1), @cDelimiters) = 0
select @wordcount = @wordcount + 1, @k = @k + 1, @BegOfWord = @k, @flag = 1 -- Skip over the first character in the word. We know it cannot be a break character.
end
set @k = @k + 1
end

if charindex(substring(@cSrting, @k-1, 1), @cDelimiters) > 0
set @k = @k - 1
if @flag = 1
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cSrting, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- last word
end

return
end
GO


______________________________________________________________

Ladies and Gentlemen,

I am pleased to offer, free of charge, the following string functions Transact-SQL:

AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).

AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.

More than 5000 people have already downloaded my functions. I hope you will find them useful as well.

For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115

Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115

With the best regards.