GETALLWORDS Inserts the words from a string into t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

GETALLWORDS Inserts the words from a string into t

Author

Topic

Igor2004
More clever than you

78 Posts

Posted - 2005-08-25 : 14:25:23

[code]
-- 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
[/code]

______________________________________________________________

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.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-25 : 14:40:50

Not again.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 04:24:44

you sure like to complicate things igor...
this can be done so much simpler

i'm really sorry brett...

Go with the flow & have fun! Else fight the flow

Igor2004
More clever than you

78 Posts

Posted - 2005-08-26 : 11:17:48

to spirit1
O.K.
Show, please, how simpler.
Probably to see here more simple code ?

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 11:52:49

[code]
DECLARE @x varchar(8000), @z int
SELECT @x = 'There once was this crazy Russian from Canada', @z = 1
DECLARE @y table (WordOrder int IDENTITY(1,1), word varchar(8000))

WHILE @z > 0
BEGIN
SELECT @z = CHARINDEX(' ',@x)
IF @z <> 0 INSERT INTO @y(word) SELECT SUBSTRING(@x,1,@z-1)
SELECT @x = SUBSTRING(@x,@z+1,LEN(@x)-@z)
END
INSERT INTO @y(word) SELECT @x

SELECT * FROM @y ORDER BY WordOrder

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-26 : 11:55:22

Brett, I'm sorry too (for extending your suffering) but...

Igor, thank you for the very clever code. I have a question though:
Why is the English in your code comments so much clearer than the English in your posts?

Did you have help? Or maybe that is that the latest in "hand-held" dictionary/translators you're holding there in your picture?

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=16256

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 12:41:01

well igor i guess brett already did...

Go with the flow & have fun! Else fight the flow

Igor2004
More clever than you

78 Posts

Posted - 2005-08-26 : 13:02:56

to spirit1.
That Brett has answered ?, that he nothing has understood, as well as you.
Keyword is "more" !
@cDelimiters nvarchar(256) - Optional. Specifies one or MORE optional characters ...
You can post here more simple function suitable for all strings or not?
to TG
I don't speak English, all my published texts have been corrected by those for whom English language native (just as my French and Spanish texts).

Igor2004
More clever than you

78 Posts

Posted - 2005-08-26 : 13:11:25

-- Example
select * from dbo.GETALLWORDS('There once was this slow-witted, stuck-up American from USA',' -,')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-26 : 13:38:26

>>dbo.GETALLWORDS('There once was this slow-witted, stuck-up American from USA',' -,')
ohhh! Good one, Ig

>>well igor i guess brett already did...
And in the smooth sql-stylings of RobVolk, here's another:
(assumes a Tally table exists)


-----------------------------------------
--here's a tally table in case you don't have one handy
create table tally (n int primary key) 
go
declare @i int
set @i = 1 
while @i < 250
begin
	insert tally values (@i)
	set @i = @i+1
end
go
-----------------------------------------

declare @x nvarchar(4000), @del nvarchar(255), @dl int
select	@del = N'RobVolk', @dl = len('.'+@del+'.')-2
SELECT @x = N'ThereRobVolkonceRobVolkwasRobVolkthisRobVolkcrazyRobVolkRussianRobVolkfromRobVolkCanada'
set @x = @del + @x + @del

select	substring(@x, n+@dl, charindex(@del,@x,n+@dl)-n-@dl)
from	(select @x s) phrase cross join tally t
where	substring(@x, n, @dl) = @del
and	n < len(@x)-@dl

Be One with the Optimizer
TG

Igor2004
More clever than you

78 Posts

Posted - 2005-08-26 : 13:58:39

to TG
You are able to read attentively?
"Note that GETALLWORDS( ) uses EACH of the characters in @cDelimiters as INDIVIDUAL delimiters, not the entire string as a single delimiter."
_______________________________________________________
to spirit1
Once again - whether you can give an example more simple FUNCTION (instead of a confused code), doing the same that GETALLWORDS suitable for ALL strings.
Keywords are FUNCTION, SAME, GETALLWORDS, ALL STRINGS.
______________________________________________________--
to X002548
Communists wrote to 80 years that 23 000 000 Americans are illiterate,
That is 23 000 000 Americans are not able to read, fill the simple questionnaire. I certainly hate communists, but is similar, that the number of illiterate Americans since then has increased (together with arrogance).
Why you think that others more silly you though often happens on the contrary?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-26 : 14:11:29

quote:
"Note that GETALLWORDS( ) uses EACH of the characters in @cDelimiters as INDIVIDUAL delimiters, not the entire string as a single delimiter."

Thanks for noticing the added functionality! Feel free to use my code to improve yours since it is not limited to your 4 possible delimiters and can handle variable length delimiters.

Be One with the Optimizer
TG

Igor2004
More clever than you

78 Posts

Posted - 2005-08-26 : 14:34:20

to TG
"to your 4 possible delimiters and can handle variable length delimiters."
I have understood nothing, what is 4 possible delimiters ?
Explain, please more in detail.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-26 : 14:46:22

Why must we go through this again!?!?

Past and Current Discussion Summary:

Igor: Here are some clever functions
SQLTeam: Great, but if I needed a function to do something specific, I could write one that accomplishes that specific task only.
Igor: but I've found all of the errors and I guarantee my code!!
SQLTeam: So are you going to pay us if we find a bug?? These issues are not hard to solve... so we will address them if need be.
...

EDIT: There is always more than one way to accomplish any task... for example, why not write a dll and utilize xp calls to dlls for these things...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

TallCowboy0614
Starting Member

17 Posts

Posted - 2005-08-26 : 14:58:59

quote:
Originally posted by TG
Thanks for noticing the added functionality! Feel free to use my code to improve yours since it is not limited to your 4 possible delimiters and can handle variable length delimiters.

OUCH!!! That's gonna leave a scar...

(but not bad for one-o-them-thar slow-witted, stuck-up Americans (from USA, of all places for an American to be from...))
;)

_________________________________
aka "Paul"
Non est ei similis.

"He's not the Messiah. He's a very naughty boy!" - Brian's mum

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 14:59:47

well just for the fun of it

i haven't used a tally table in the split becuse while is more readable.
will it do?


create table tally (n int primary key) 
go
declare @i int
set @i = 1 
while @i < 250
begin
	insert tally values (@i)
	set @i = @i+1
end
go

CREATE FUNCTION dbo.Split
(
	@RowData varchar(8000),
	@SplitOn varchar(256)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data varchar(4000)
) 
AS  
BEGIN 

	if @SplitOn is not null 
	begin
		select 	@RowData = replace(@RowData, substring (@RowData, n, 1), ' ')
		from 	tally
		where	charindex(substring (@RowData, n, 1), @SplitOn) > 0
	end
	set @SplitOn = ' '
	While (Charindex ( @SplitOn, @RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select Data = ltrim(rtrim(Substring( @RowData, 1, Charindex ( @SplitOn, @RowData) - 1)))
		Set @RowData = ltrim(rtrim( Substring( @RowData, Charindex ( @SplitOn,@RowData)+1,len( @RowData))))
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

go
declare @cString varchar(8000)
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.split (@cString, default)     
select * from dbo.split (@cString, ' ,.')

go
drop table tally
drop function split

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 15:02:24

seems i'm the only non american besides igor in this thread...
hey igor... do i qualify as stuck-up American too?

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 15:06:49

Damn....you're right, I should've used flags in my code...I haven't used them since, I think it was, ummm, third grade. So I've forgotten how to use them.

Who's the chick?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 15:10:38

she's cute...

Go with the flow & have fun! Else fight the flow

Igor2004
More clever than you

78 Posts

Posted - 2005-08-26 : 15:27:36

to Seventhnight
"So are you going to pay us if we find a bug?? "
About what sum there is a speech?
During what time SQLTeam will search for error?
Whether SQLTeam will pay the same sum to me if SQLTeam will not find an error?
(my error, not the bug of SQL Server)
__________________________________
"EDIT: There is always more than one way to accomplish any task... for example, why not write a dll and utilize xp calls to dlls for these things..."
I completely agree. But not about this is a speech.
Perfectly, write and post here, please, your code.
__________________________________
spirit1
Your function (more simple!) works with ALL strings, and your function is without errors ? Answer, please, simply - my function works with all strings and my function is without errors.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 15:36:52

test it... if you find an error report it or fix it...
i don't need it... i wrote it per your request

Go with the flow & have fun! Else fight the flow

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources