Counting number of word on each row 1

[damipera]

Hi guys, how do you count number of words on each row if i have a table like this:

mytable
--------
id contents
1 ...is the most popular Content Management System for blog engine....SQLserver and then just run it and the script for SQLserver database will executed automatically

2 SQLserver is one of database management system. SQLserver community...This version of SQLserver, already enough ....

i cant use the Count() as this will not be fitting to the use. i would like to count the number of times, say 'SQLserver', has been mentioned in each row/record.

thanks for any ideas.

 

[gmmastros]

Ah... the good ole replace trick. Here's how it works...

First, you calculate the length of the string. Then you calculate the length of the string after you replace you search string with empty string.

Ex:
"SQLserver is one of database management system. SQLserver community"

Length of string above is 67 characters.

" is one of database management system. community"

Length of string is now 49 characters

67-49 = 18 characters

SQLserver has 9 characters in it. So, 18/9 = 2

Putting it all together...

Select (Len(Contents) - Len(Replace(Contents, 'SQLserver','')))/Len('SQLserver') As WordCount

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[damipera]

hi george, it works! thanks. but do you have any idea how should i go about doing this in a text data field?

i am using sql server 2000 and i read that it's easily done in sql server2005 using cast?

i am using:
select convert(varchar(length),textcoloumn) from mytable

any thoughts?

thanks

 

[gmmastros]

Urgh. SQL2000 and text columns.

Everything I once knew about text data, I've forgotten. If the data (stored in the text column) is less than 8000 characters, you can convert to varchar first.

Select (Len(Convert(VarChar(8000), Contents)) - Len(Replace(Convert(VarChar(8000), Contents), 'SQLserver','')))/Len('SQLserver') As WordCount

Just curious, why do you want to count the words?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[damipera]

thanks very much george for the tip.

it's just the boss wanting to keep track of some products on the fields.

have a nice day!

thnx