Many databases support what is known as "wildcard" searching.
Just as in some card games, where jokers can act as any
card the player desires, so there are two special characters
you can enter that act as "search jokers", matching either
as many characters as you like or exactly one character.
Technically, these are known as "wildcards".
The two wildcard characters are the percent sign (%)
and the underscore (_).
In addition, there is another built-in feature known as "soundex",
which enables you to search for words that sound like other words.
This will be discussed after the explanation of wildcard searching.
Single-Character Matching
Supposing you are searching for a database entry that was made
by either a Smith or a Smyth. You cannot remember or do
not know which it is. But you want to carry out a search that
will stand a good chance of returning the results you want without
getting too many irrelevant results.
To find all the Smiths and Smyths, you can enter the following
search criterion:
Sm_th
The underscore says, "Match any one, and exactly one character in
its character position". Thus Smith and Smyth will both
match this search criterion because it does not matter what is in the
third position; provided the first, second, fourth, and fifth characters
all match, you will get a matching result.
Multiple-Character Matching
Now let's suppose you are looking for an entry that was made by either
a Smith or a Smithson. It could also have been made by a Smithson-Jones
or a Smith-Jones - you don't know or cannot remember:
All you do know is that it was a Smith something or other.
There is a way to achieve this by using a search such as the following:
Smith%
The percent sign says, "Match any number of characters (which can
legitimately be zero) at that position". Thus Smithson and Smith-Jones
will both match, but not Smythson because of the "y".
Smithe and Smitheson will also match.
Now let's get a bit more complex still. You know that the entry was made
by someone called Smith something or other Jones, but you do not know
how the "smith" is spelled, or whether in fact it wasn't even
"Smythson"! Consider the following search criterion:
sm_th%Jones
The underscore says that we must match exactly one character after the
"sm" and that it does not matter what that character is.
The next two must be "th". After this, we can have as many
characters as we like provided the word "Jones" comes afterwards.
Thus all the following names will match:
Smith-Jones
Smythe-Jones
Smithson-Jones
SmithJones
Smytheson Jones
The fourth one, SmithJones, matches because the percent sign can legitimately
match exactly zero characters.
Begins, Ends, Contains
In fact, the person has probably entered their first forename or a title
rather than just his/her surname. Thus the above searches may not work
at all! There are two things you can do to get round this problem.
The first is to put a percent sign at the start of the string, thus:
%Sm_th%Jones
and the other is to use either the "Ends" or "Contains"
radio button. You see, saying that the search string is to end with
(or contain)
the entered text is to say that you want the search engine to put a
percent sign at the front for you, thus saving you from having to enter it.
Thus the above search, or specifying Ends With, will match the following
names:
James Smith-Jones
Smythe-Jones
Mr. Smithson-Jones
John SmithJones
Derek Smytheson Jones
However, these will not be matched...
Smith-Jones, Mr
Smythe-Jones, Derek
...because the search is actually specifying that the name had to end
in Sm_th%Jones. Should we want to catch these ones as well, we need to either
use a percent sign at the end of the search string, like so:
Sm_th%Jones%
and use the Ends search method, or, much better, to leave the ending
percent sign off and use the Contains radio button.
The Contains radio button will cause a percent sign to be added at both
the beginning AND the end of the search string, thus forming %Sm_th%Jones%.
This will cause Sm_th%Jones to be matched anywhere in the field.
Soundex
Soundex is a database-provided feature that enables you to search for words
that sound like another word. Thus "hair", "heir",
and "hare" will all match on a Soundex search. It cannot be used
in conjunction with the wildcards described above - either the whole field
matches on a Soundex match or nothing in that field does. There is one limitation
to its use however, and this is not something that anything can be done about.
Certain words that you would think should match actually won't. For example,
"air" and "ear" will not match, neither will "heir"
and "ear".
The reason for this is technical: Soundex works by preserving the first
letter of the words, and then working out a code to determine whether they
are likely to sound similar in English. Now the initial letters of "ear"
and "air" are different, thus the Soundex codes won't match, thereby
leading to a matching failure.
Soundex matching has been included as it may be very useful in certain
circumstances, subject to the above limitation.
A huge thank you and tons of hugs to Angus Cook for volunteering his time and efforts to
get Heaven's Gate working once again. This could not have been accomplished without
his help. We are most grateful for your help, Angus! :)