Tip:
Highlight text to annotate it
X
I'd made a copy of
odb21 query where part 2
and I'm calling this one odb22 query
wild cards let's make a query
I'm going to make this in design view
I'm going to add our table
add
last name, first name,
Department, and group a few videos back when we are looking at filters
I was able to select all the last names that started with the letter T
we're going to do that same thing right now
using the wildcard. I'm coming down to the criterion
under last name and I'm going to type like
single quote capital T
astrisks single quote
the astrisks will be a wildcard let'*** F5
to run this query and we see the names
Taylor, Thompson, and Thomas anything that starts with the letter T
Let's go over to SQL View and see what that looks like
I'll reformat this to make it easier to read
our SELECT and our FROM look good
in our WHERE we got the last name fieldname
in double quotes which is what we expect like
is all uppercase not in quotes
then we have single quote T
percent sign single quote
in SQL View we use a percent sign this
is another difference between Design view in SQL view
you in design view we use the asterist as the wildcard in SQL View
use a percent sign as the wildcard And when I say wildcard
I mean something that can replace one character,
many characters, or even no characters at all.
let's change T to Th
percent sign... pressing f5
now that limits us Thompson and Thomas
let's find all the last names that
end with the letter n pressing f5
we see we've got 8
let's go back into
design view we see that our percent sign
change back to an asterist. let's find all the last names
that have the letter 'a' in it For that we want asterist
a asterist. I need to hit enter
then f5 that looks pretty good
but let's add in the sort by last name
and we see the first name in the list
is Adams... this list
is pretty good but to
truly answer the question we need to check for an uppercase A
the in first position. so we need to say
like asterisks a asterisks or like
asterisks uppercase
A asterisks we see we've got
12 in our results here I'm going to
hit enter to lock in our criteria
then hit f5 in now would get 13
we added Anderson into our list
because that only has an uppercase A
in the first position in no other occurrences of lower case a
let's go over to SQL View
I'll reformat the SQL
here we see in our WHERE we've turned
are asterisk back into a percent sign
and it's also added some parentheses around our
equation here. to make last name like
lower case a or last name like
upper case A. let's change this OR
to an AND
to run the query by pressing f5
now we only have one record Adams that has both
in uppercase A and a lower case a
let's save this query save as...
we'll call this odb21 query
wildcard lower case a dash upper case A
and I'll close the window
now that query worked pretty well, but what if we want to limit our wildcard
to he specific position
let's start a new query
in design view. let's add our table
we want last name, first name, department, and group
now we want to select all the last names
that have a Lower case O in the second position
to do that a come down to our criterion and I'll type
like single quote
question mark lower case o
asterist single quote. I'll press Enter to lock it in
and then press F5. we see we've got
six people with the letter o in the second position
so we see the question mark
works as a wildcard for a single position
then we're asking for the letter O and then we put in an asterists
wildcard mean anything else
some characters many characters or no characters at all
following that lower case o Let's see what this looks like
in SQL View I will reformat the SQL
you we see the question mark
has turned into an underscore for positional wildcard
justice are asterisks has changed to a percent sign
for our wild-card let's save this
as
O in the second position now let's go back over
to design view. I'm going to
change our search criterion for the last name
to be capital letter J
single position wild-card,
single position wildcard, single position wildcard.
then son single quote
pressing enter to lock that in
Then I'll press f5 to run it and we see
Jackson and Johnson because they both start with the capital J
in end with S O N with three characters in the middle
let's see what this looks like in SQL view
I'll reformat this to make it look nicer
we see are question marks have turned back in
to the underscore and it's difficult to tell whether there's three or four
but if I put my cursor up there and arrow over
we can see that it's 3 underscores
representing are three wildcards
let's Save this as...
clicking OK
closing this out
and saving our database all
all cool all