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.
| Author |
Topic |
|
hapytran
Starting Member
8 Posts |
 Posted - 2005-09-13 : 00:07:30
|
| I need a user-defined function that return the number of words in a field of database. For axample, a table named xxx:--------------------------------------------------------------id | description | note--------------------------------------------------------------1 | Microsoft SQL Server articles, news and forums | nothing................if i query "select CountWords(select description from xxx where id=1), the expected result is 7.Anyone wrote this function? Pls share, thanks. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-09-13 : 12:06:49
|
| The easiest is this:_________________________________declare @s varchar(100)select @s='Microsoft SQL Server articles, news and forums'select len(rtrim(ltrim(@s)))-len(replace(rtrim(ltrim(@s)),' ',''))+1-----------------------------------------but as all easy solutions it has problem that is if words in your string are delimited by several spaces instead of only one - result will be incorrect |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-13 : 12:17:34
|
quote:
Originally posted by Stalker
The easiest is this:_________________________________declare @s varchar(100)select @s='Microsoft SQL Server articles, news and forums'select len(rtrim(ltrim(@s)))-len(replace(rtrim(ltrim(@s)),' ',''))+1-----------------------------------------but as all easy solutions it has problem that is if words in your string are delimited by several spaces instead of only one - result will be incorrect
Very NiceSince it's varchar I don't think you need to trim it though.You could alway try eye-gores, more clever than you stuffhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54333Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-13 : 16:12:18
|
Who could forget the IGOR.GETWORDCOUNT() function?Clever, tested, and guaranteed!quote:
Originally posted by X002548
quote:
Originally posted by Stalker
The easiest is this:_________________________________declare @s varchar(100)select @s='Microsoft SQL Server articles, news and forums'select len(rtrim(ltrim(@s)))-len(replace(rtrim(ltrim(@s)),' ',''))+1-----------------------------------------but as all easy solutions it has problem that is if words in your string are delimited by several spaces instead of only one - result will be incorrect
Very NiceSince it's varchar I don't think you need to trim it though.You could alway try eye-gores, more clever than you stuffhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54333Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
CODO ERGO SUM |
|
|
|
|
|
|
|
|
Count word function?