Search Tips and Tricks
The search conditions used for full text searches are character strings composed of words, phrases, logical operators, and parentheses for grouping precedence.
Aside from white space characters, which are considered to be
delimiters in search strings, special characters include the double quote used
for delimiting phrases, the asterisk used for prefix, postfix, and substring
matching, and parentheses. The reserved words include the logical operators OR,
AND, NOT, NEAR. Noise words, words ignored by the FTS engine, inlude the following:
ABOUT AFTER ALL ALSO AND ANOTHER ANY ARE BECAUSE BEEN BEFORE BEING BETWEEN BOTH BUT CAME CAN COME COULD DID DOES EACH ELSE FOR FROM GET GOT HAD HAS HAVE HER HERE HIM HIMSELF HIS HOW INTO ITS JUST LIKE MAKE MANY MIGHT MORE MOST MUCH MUST NEVER NOW ONLY OTHER OUR OUT OVER SAID SAME SEE SHOULD SINCE SOME STILL SUCH TAKE THAN THAT THE THEIR THEM THEN THERE THESE THEY THIS THOSE THROUGH TOO UNDER USE VERY WANT WAS WAY WELL WERE WHAT WHEN WHERE WHICH WHILE WHO WILL WITH WOULD YOU YOUR
The precedence of the logical operators from lowest to highest is OR, AND, NOT, NEAR. You can use parentheses to change the precedence grouping. For example, the search condition ‘meeting and request or changelog’ finds records that have both the words "meeting" and "request" or the word "changelog". Changing the precedence with parentheses to ‘meeting and (request or changelog)’ causes the search condition to find records that have the word "meeting" and either of the words "request" or "changelong".
Double quotes are used as phrase delimiters. In addition, special characters can be enclosed inside double quotes to ensure that they are unchanged by the search condition parser. For example, to search for a parenthesis, it is necessary to enclose it in double quotes in order to keep it from being treated as a precedence operator in the search condition itself. To search for a physical double quote, use two of them in a row.
The logical OR operator produces a "true" result when either operand evaluates to true. The logical AND operator produces a true result only when both of its operands evaluate to true. The logical NOT operator produces a true result when its single operand has a false result. The NEAR operator (proximity operator) is similar to the AND operator in that both of its operands must be found. In addition, it requires that its operands be within a certain physical distance of each other for it to return a true result. The default distance for the NEAR operator is 8 words. To use a proximity value other than the default, specify the distance as a parameter to the NEAR operator. For example ‘medical near(15) doctor’ will evaluate to true for records where the word "medical" is within 15 words of "doctor".
Search words that do not have logical operators specified are assumed to have an implied AND operator between them. For example, the following two search conditions are equivalent:
‘mechanical AND engineer’
And the following two are equivalent:
‘computer programmer or software developer’
‘(computer AND programmer) OR (software AND developer)’
Individual search words that are not enclosed in double quotes can be searched for as exact matches, prefix matches, postfix matches, or substring matches. This behavior is controlled through the use of the asterisk (*) character.
· Exact match. Simply specify the word in the search condition. The word will be matched if it is found exactly as given in the text. For example, the search word "special" will match only "special" in the text (or upper case versions if it is not a case sensitive index).
· Prefix match. Place an asterisk at the end of the word to match all words that begin with the given characters. For example, the search word "special*" will match "special", "specialty", "specialization", etc.
· Postfix match. Place an asterisk at the beginning of the word to match all words that end with the given characters. For example, the search word "*ation" will match words "station", "specialization", "citation", etc.
· Substring match. Place an asterisk at both the beginning and end of the word to match all words that contain the given characters. For example, the search word "*lock*" will match the words "locker", "antilock", "blocking", etc.
Important Note Exact matches and prefix matches provide the best performance. Both postfix and substring matches require that the entire FTS index be scanned in order to satisfy the search. This is still much more efficient than searching the actual data, but it is less efficient than prefix and exact matches, which can be resolved with O(Log N) searches.
Multi-word phrases in search conditions match identical phrases in the text being searched. For example, the search condition ‘ "alpine skiing" ’ will match only records that have the exact words "alpine" and "skiing" in that order in the text with no other non-noise words between them.