Help...I need to count number of occurences of a word in a field

I found this function on the web

Function GetSubstringCount(strToSearch, strToLookFor, bolCaseSensative)
If bolCaseSensative then
GetSubstringCount = UBound(split(strToSearch, strToLookFor))
Else
GetSubstringCount = UBound(split(UCase(strToSearch),
UCase(strToLookFor)))
End If
End Function

Here is my tweak'd part...
str = rs("pageData")
strSubstring = request.form("keyword")
iCount = GetSubstringCount(str, strSubstring, False)
response.write iCount 'this gives me my total number

Here is my select statement...

sql = "SELECT " _
&
"pageInfo.productID,pageInfo.pageID,pageInfo.pageTitle,pageI nfo.pageData,
" _
&
"meta.pageID,meta.productID,meta.metaTitle,metaDesc,products .productID,products.listName
FROM pageInfo,meta,products " _
& "WHERE products.productID=pageInfo.productID " _
& "AND pageInfo.pageID=meta.pageID AND
pageInfo.productID=meta.ProductID " _
& "AND pageData LIKE '%"&request.form("keyword")&"%' "





I want to sort my data by the largest occurance of the keyword that I
am searching for -> request.form("keyword")

I'm new to the string manipulation thing, but am eager to be shown some
insight.

It's an access db fyi

Many Thanks oh yet again!

Re: Help...I need to count number of occurences of a word in a field

joew@vca.com wrote:
> I found this function on the web
>
> Function GetSubstringCount(strToSearch, strToLookFor,
> bolCaseSensative) If bolCaseSensative then
> GetSubstringCount = UBound(split(strToSearch, strToLookFor))
> Else
> GetSubstringCount = UBound(split(UCase(strToSearch),
> UCase(strToLookFor)))
> End If
> End Function
>
> Here is my tweak'd part...
> str = rs("pageData")
> strSubstring = request.form("keyword")
> iCount = GetSubstringCount(str, strSubstring, False)
> response.write iCount 'this gives me my total number
>
> Here is my select statement...
>
> sql = "SELECT " _
> &
> "pageInfo.productID,pageInfo.pageID,pageInfo.pageTitle,pageI nfo.pageData,
> " _
> &
>
"meta.pageID,meta.productID,meta.metaTitle,metaDesc,products .productID,produ
cts.listName
> FROM pageInfo,meta,products " _
> & "WHERE products.productID=pageInfo.productID " _
> & "AND pageInfo.pageID=meta.pageID AND
> pageInfo.productID=meta.ProductID " _
> & "AND pageData LIKE '%"&request.form("keyword")&"%' "
>
>
>
>
>
> I want to sort my data by the largest occurance of the keyword that I
> am searching for -> request.form("keyword")
>
> I'm new to the string manipulation thing, but am eager to be shown
> some insight.
>
> It's an access db fyi
>
> Many Thanks oh yet again!

It is not possible to do this in your query. Just forget that idea. I think
the easiest way to do this is to create an ad hoc recordset containing all
the fields from your connected recordset, plus a field to contain the
wordcount. This recordset should be created before you even open the
connection. Then use a loop to append the records from the connected
recordset into the ad hoc recordset, using your function to populate the
wordcount field. Close the connected recordset and its connection. Set the
ad hoc recordset's Sort property to the wordcount field, and then write the
data to the response.

I don't have enough information to provide an example since I don't know
your datatypes, etc. Here's an example:
http://www.outlookcode.com/d/code/popcombobox.htm

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.