Sort Results by Number of Hits

[seaofcrisis]

In MSSQL, is there a way to count the number of instances of a
substring within a string, so that I can sort by that?

For example:
table tst contains one column: tst_data

if tst_data = "the man with the plan"

I'd want a function that counts the occurances of "the"

count_substring(tst_data,'the') = 2

Basically, I'm making a search engine and I'd like to put the most
relavent hits at the top of the page.

[louis]

http://www.sqlteam.com/item.asp?ItemID=5857

[Hugo Kornelis]

On 4 Feb 2005 11:46:46 -0800, se*********@gmail.com wrote:

In MSSQL, is there a way to count the number of instances of a
substring within a string, so that I can sort by that?

Hi seaofcrisis,

DECLARE @test varchar(100), @srch varchar(10)
SET @test = 'the man with the plan'
SET @srch = 'the'
SELECT (LEN(@test) - LEN(REPLACE(@test, @srch, ''))) / LEN(@srch)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)