I'm attempting to write an SQL statement that will perform a keyword search on a memo field in an Access database. I require exact matches only. I am using VBscript. If I use
% keyword % with a space at the beginning and end of each word, it will return exact matches but will not return a record when the word is preceeded or followed by a carriage return in the memo field. I found your suggestion of using ALT-0013 to find the carriage return and it works, IF I type in the exact word. But now if I type in a portion of a word only, it still returns the record. It seems that it recognizes the ALT-0013 when a carriage return is present, but ignores it when there isn't one. Can you help with this question? Thank you.
What you need to do is use multiple tests in the WHERE clause. Here's how I would search for the exact match --
where memofield like '% keyword %' or memofield like 'keyword %' or memofield like '% keyword' or memofield like '%'+chr(13)+'keyword %' or memofield like '% keyword'+chr(13)+'%'
These five tests are to find the keyword
- in mid-sentence
- at the start of the entire field
- at the end of the entire field
- at the start of a line after a line break
- at the end of a line before a line break
So you can see, an exact match is trickier than just padding it with blanks.
The bad news is, it's even worse than the above. You will have to search for quite a few additional combinations, to allow for punctuation. For example, consider the keyword "fun" in this sentence --
Fun? That might be fun, but not too much fun; too much "fun" is no fun.
Notice that the keyword can be followed immediately by a question mark, comma, semi-colon, quote, or period. The good news is that you can search for those other special characters following the keyword like this --
or memofield like '% fun[?,.;"]%'
This detects punctuation immediately after the keyword. You will still want to test for a quote in front of the keyword --
or memofield like '%["]fun %'
But notice that each of the above conditions (punctuation after and before the keyword) was coded with a blank on the opposite side. So to be thorough, you would have to code up multiple conditions in combination with the other possibilities -- start of the field, end of the field, start of a line, and end of a line. All in all, I think you are looking at maybe 15 or more combinations.
Are you sure you need exact matches? I would just ignore the blanks, line breaks, and punctuation, and do a simple partial match --
where memofield like '%keyword%'
Sure, you'll get "functional" and "funny" along with "fun" but it's a lot easier to code and might be faster in execution, too.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.