Seach for whole word with ASP / /MS Access

Discussion in 'ASP General' started by Giles, May 18, 2005.

  1. Giles

    Giles Guest

    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
    Giles, May 18, 2005
    #1
    1. Advertising

  2. Giles wrote:
    > 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
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], May 18, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Lincoln
    Replies:
    3
    Views:
    438
    Chris Lincoln
    Jul 19, 2006
  2. \A_Michigan_User\
    Replies:
    2
    Views:
    871
    \A_Michigan_User\
    Aug 21, 2006
  3. jambiani
    Replies:
    4
    Views:
    374
    Andy Dingley
    Sep 4, 2006
  4. Kevin
    Replies:
    1
    Views:
    308
    Victor Bazarov
    Mar 8, 2006
  5. sherry
    Replies:
    0
    Views:
    413
    sherry
    Apr 15, 2009
Loading...

Share This Page