Count text appearing in a field

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

SQL Server 2000 Forums

SQL Server Development (2000)

Count text appearing in a field

Author

Topic

UberMeister
Starting Member

1 Post

Posted - 2010-01-21 : 04:52:36

Good day peeps,

I am suck with a problem, in the quest to create a small but powerful search engine for our product, I have crashed in to a problem with writing my sql query.

Here is my table: tblMain

UniqueID | Description
------------------------------
UniqueID, is an auto generated number.
Description is a varchar[1000]

Here is an example of the 'Description' field: "The crazy dog spoke to his dog friend's mother in dog language."

What my problem is, I want to be able to search the 'Description' field, for how many time the word "dog" appears.

This is what i have so far...
SELECT UniqueID FROM tblMain WHERE Description = '%dog%';
But this only gives me the field it is in...I have tried working in a COUNT somewhere but just couldn't get it...I even loaded the results into a temp table, but that would not be efficient enough.

Any help would be much appreciated!

There is no CTRL+Z in life.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 05:05:02

Here is an approach:

declare @test varchar(1000)
declare @search varchar(255)
set @search='dog'
set @test='The crazy dog spoke to his dog friend''s mother in dog language.'

select
@test as example,
(datalength(@test) - datalength(replace(@test,@search,''))) / datalength(@search)
as count_search

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 05:05:57

Try this too

declare @var varchar(1000)
set @var='The crazy dog spoke to his dog friend''s mother in dog language.'

select (len(@var)-len(replace(@var,'dog','')))/len('dog')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 05:06:39

Not sure if it helps in your case, but we "split" descriptions into single words and store them in a keyword table. (We only store distinct words, but you could store Word+Count, or just every word (and then do a COUNT(*) to find how many)

We gave up on DESCRIPTION LIKE '%' + @UsersSearchWord + '%' (well, we had something more sophisticated than that!) and we didn't want to use the FreeText tool because our performance tests were dire ... but having said that FreeText in SQL2008 looks a whole boatload better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources