Giles said:
Is there a way to get records containing a whole word? I've heard
regular expressions can do it, but I can't make one work in an ASP /
MS Access SQL Query
sSQL="SELECT PageTitle FROM Pages WHERE PageTitle LIKE '%cat%' "
rs.open etc
returns scatty, catatonic etc. Thanks for your help
Giles
The surest way to do this would be to create an "index" table. I would not
perform this task in asp. VBA code would be best. You can use Windows
Scheduler to casue the task to run periodically. you should ask in an Access
newsgroup for details about running VBA code from the command-line used to
open your database in Access.
Here are the bare bones (this is untested "air" code) of what this task
would do (I'm assuming your table has an autonumber PageID field ...):
First create the PageIndex table. It should have two fields: PageID and
Words
Sub RebuildIndex()
dim cn as adodb.connection
dim rs as adodb.recordset
dim cmd as adodb.command
dim ar as variant
dim arParms as variant
dim sSQL As String,
dim data as string
dim i as integer
set cn = Application.CurrentProject.AccessConnection
'first, clear the pageindex table:
cn.execute "delete from PageIndex",, _
adCmdText + adExecuteNoRecords
set rs = new adodb.recordset
rs.cursorlocation=adUseClient
rs.Open "Select PageID, PgeTitle FROM Pages", cn,,,adCmdText
set rs.activeconnection=nothing
sSQL = "INSERT INTO PageIndex (PageID, Words) VALUES(?,?)
set cmd=new adodb.command
cmd.activeconnection=cn
cmd.CommandText=sSQL
do until rs.eof
data=rs(1)
data = replace(data,"."," ")
'repeat to remove the other punctuation marks
'hopefully somebody wil jump in with a regexp patrern to
'replace them all in one shot
'Optionally, use regexp to eliminate "nuisance" words, such as articles
ar=Split(data, " ")
for i = 0 to ubound(ar)
arParms=Array( rs(0) , ar(i))
cmd.Execute ,arParms,adCmdText + adExecuteNoRecords
next
loop
End Sub
Now, querying for specific words will be as simple as:
select DISTINCT PageTitle
FROM Pages p INNER JOIN PageIndex i
ON p.PageID = i.PageID
WHERE Words = "cat"
HTH,
Bob Barrows
