If i make a:



SELECT * FROM table WHERE text Like %robert%



Is there a way to know how many hits it got in that text on each row ?





Cheers,

Robert Larsson!

The way I have achieved this is with SQLServer and the use of freetext

databases.



The sql to run is as follows:



SELECT TOP 100 * FROM table AS FND_TBL, 

	CONTAINSTABLE(table, *, 'robert') AS KEY_TBL 

WHERE FND_TBL.ID = KEY_TBL.[Key] AND text='robert'

ORDER BY KEY_TBL.RANK DESC



You have to have already created the freetext database and populated it.

This is bloody fast - I have a SQLServer table with about 500,000 records in

and it take about 1 second to do a search for something like 'blue and

stapler' - it takes llonger to return the resultsset via my browser that it

does to do the search.



I hope this helps - if you are not running SQL server then I don;t have a

clue, apart from writing code to do INSTR's on each record.





-----Original Message-----

From: Robert Larsson 

Sent: 22 May 2000 10:24

To: ASP Databases

Subject: [asp_databases] Is there a way of knowing how many hits a

"where" sql statement got ?





If i make a:



SELECT * FROM table WHERE text Like %robert%



Is there a way to know how many hits it got in that text on each row ?





Cheers,

Robert Larsson!