Whole word search

[Davighi]

Please forgive my ignorance but I am trying to do a search and am presently using 'like' which works but returns a lot of irrelevant results i.e partial words that contain the search term.

Is there an easy way to search only for whole words within records that match the search word/s

 

[KenHigg]

Could you use spaces before and after the search criteria:

like " mywordone "

???
kh

 

[Davighi]

I thought of that but would it not miss the first or last words out?

 

[KenHigg]

Hum..

Then maybe:

like " mywordone " or like "mywordone " or like " mywordone"

???
kh


edit: Actually the " mywordone " pc would seem redundant, huh...

 

[Davighi]

Looks like it should work I will try it, thanks

 

[Davighi]

I tried it and the results are much better, however, it still brings up results where the word begins or ends with the search word.

Is there something I can add before "mywordone " and after " mywordone" to stop this partial word match?

 

[KenHigg]

Could you start by placing a space on each end of the string then use " myword "?


kh

 

[Davighi]

The database has 150,000+ records and the search searches 4 feilds so it would not be ideal but possible yes.

 

[KenHigg]

Hum..
Seems a reg exp solution would be posible but I really not up on using them...

Maybe the thread'll catch the eye of a reg exp guru...

kh

 

[Davighi]

Thanks for the help so far Ken I have sent a private message so you can see the site I am trying to sort out and the problem I have

If you search for "car" in the UK you get 17703 results including car, cars, carpets, Montecarlow, heathcare etc...

 

[KenHigg]

Cool site. You doing the back end with Acess?

Failing to find a solution with req exp, I would just add the space. How is the field set up that contains the data to be searched? Is it a memo? Is it something like:

"word1 word2 word3"

How long can the string be?

kh

 

[Davighi]

I'm not the database expert just trying to speed his job up as he still has quite a bit to do.

The answer to your first question is yes we are using Access and querying directly from the asp code.

The search searches four feilds all text if I remember correctly, title, description, keywords and a fourth I can't remember at the moment.

The title has up to 80 characters, description a little bit more and the keywords are comma separated.

 

[KenHigg]

Hum...

It's sounds more and more like case to use regular expressions. Maybe you can repost with a title like 'Help with reg exp's' or search this forum/web?

kh

 

[Davighi]

Thanks again Ken

If I don't get any more relpies I will repost.

 

[Jud]

You can do it with LIKE

I had the same problem, trying to search for whole words in a column without the use of functions. The follwing statement does the job (at least on Access through ODBC under ASP). It handles punctuation and everything.

SELECT * FROM table WHERE
(' ' & columnname & ' ' LIKE '%[!a-z]" & searchword & "[!a-z]%')

I spent 2 days monkeying with InStr and it did finally work out but it was very ugly. This is a nice elegant solution.