473,574 Members | 2,235 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Seach for whole word with ASP / /MS Access

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

Jul 22 '05 #1
3 1728
where PageTitle like '% cat %'
--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Giles" <Gi***@NoSpam.c om> wrote in message
news:uK******** ********@TK2MSF TNGP10.phx.gbl. ..
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

Jul 22 '05 #2


"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:eb******** ********@TK2MSF TNGP14.phx.gbl. ..
where PageTitle like '% cat %'


But that won't return records containing " cat!" or " cat," or " cat." etc.

Not sure there is a way of doing it without using lots of OR clauses to
cover the above cases. Certainly not going to be a speedy query.

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
Jul 22 '05 #3
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.connectio n
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.Cur rentProject.Acc essConnection

'first, clear the pageindex table:
cn.execute "delete from PageIndex",, _
adCmdText + adExecuteNoReco rds

set rs = new adodb.recordset
rs.cursorlocati on=adUseClient
rs.Open "Select PageID, PgeTitle FROM Pages", cn,,,adCmdText
set rs.activeconnec tion=nothing
sSQL = "INSERT INTO PageIndex (PageID, Words) VALUES(?,?)
set cmd=new adodb.command
cmd.activeconne ction=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,adCmdT ext + adExecuteNoReco rds
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"
Jul 22 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
1878
by: Lord Khaos | last post by:
If I am trying to find an expression, foo, I can do something like this: rExp = /foo/gi; if(results.search(rExp) > -1){ and all work fine. however, if I want my search term to be a variable, bar: var bar= "foo";
8
3503
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At the moment the printed output is usually going to Word. It's turning into an unholy mess, because I'm having to prepare umpteen different Word...
4
1395
by: Filips Benoit | last post by:
Dear All, I want to seach the code of all forms for a string. My code below only works for open forms. I want it to work for all forms. See *** Problem line **** Thanks,
4
5311
by: Mark | last post by:
Hi all, I have a query which returns 56 results every time. 1 field of the query contains the results of an expression where 55 will contain decimal points and 1 will be a whole number. Is there a way to set the criteria of this field so that only the result containing the whole number is returned? Many thanks, Mark
0
1325
by: leeonions | last post by:
Hi there, i am trying to use regular expressions to search through a text string and replace a given whole word. take the string = "The matsat on the mat!" (bad example i know) i want to replace the whole word 'mat' with the word 'cat' to give "The matsat on the cat!" (matsat was not replaced as only whole word match, cat on the other
2
11098
by: leeonions | last post by:
Hi there, i am trying to use regular expressions to search through a text string and replace a given whole word. take the string = "The matsat on the mat!" (bad example i know) i want to replace the whole word 'mat' with the word 'cat' to give "The matsat on the cat!" (matsat was not replaced as only whole word match, cat on the other
4
3595
by: shonend | last post by:
I am trying to extract the pattern like this : "SUB: some text LOT: one-word" Described, "SUB" and "LOT" are key words; I want those words, everything in between and one word following the "LOT:". Source text may contain multiple "SUB: ... LOT:" blocks. For example this is my source text:
4
4787
by: seannakasone | last post by:
Is there a way to search a string for a whole word w/o using a regular expression? i.e. mystring.IndexOf(mypattern, *WholeWord); The reason i rather not use a regular expression is because sometimes i want to search for whole words w/o the regular expression special characters being interpreted.
8
2153
rpnew
by: rpnew | last post by:
HI, I'm working with PHP/MySql... on one page i've used this XMLHttprequest object. Now i'm developing the system on FC4/FireFox.. on my machine it works fine but if i try to access it from another PC then following happens.... From another PC which has Windows XP/FireFox/IE with firefox(on my machine it works fine) it shows the whole...
0
7720
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8230
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7810
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8096
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6451
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5618
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5299
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3739
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1337
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.