In this lesson, l want to briefly go over how to perform search using a wild card. The symbol used for wild card is the percent sign (%). Lets see how this is used to perform searches. Assume we have a table called person that has person id, first name and last name columns. Then in a query or for a search result, we want to return all persons with last name or first name that have some pattern either at the beginning, middle or at the end, or who do not have the given pattern. There is no easy way to do this without using a wild card. Lets create the table first.
CREATE TABLE person(personid ,firstname varchar(50), lastname varchar(50) )
Lets insert some data into our table
INSERT INTO person
VALUES(1,’VINCENT’,’America’),(2,’KOFI’,’2smart4school’)
Now, assume we want to find from this table people who have the character ‘a’ in their last name. We can use a wild card to accomplish this task like this.
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%a%’
This will give the result
firstname lastname
Vincent America
Kofi 2smart4school
How does it work? The wild card sign % means zero or more, that means in ‘%a%’.The query will be looking for names that either have ‘a’ either in the beginning, middle or end. If you want to specify that you want to see people who have ‘a’ as the first letter in their lastname, we do it like this
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘a%’
This will give the result
firstname lastname
Vincent America
IF you want to find people who have ‘a’ as the last letter in their last name, we do it like this
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%a’
This will give the result
firstname lastname
That means there is no matching result from the table.
Now, let’s say you want to specify the position of a pattern, say you want to find people who have ‘m’ as the third letter in their last name, u use the underscore (_) to represent one position. So our query will look like this,
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘__a%’ (– Two underscores)
This will give the result
firstname lastname
Kofi 2smart4school
Lets check people with ‘o’ as the second to last letter in their last name
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%0-‘ (– one underscore at the end)
This will give the result
firstname lastname
Kofi 2smart4school
Now lets find people who do not have say ‘a’ in their last name, we use the symbol (^) to indicate not. The query will look like this,
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%^a%’
This will give the empty resultset because all the firstnames in our person table has an ‘a’. Let’s also say you want to find names that have more than one pattern, say we want to find people whose firstname has either a ‘k’ or a ‘v’ in their firstname. We use [] to represent list of possible values to perform the search on. That means, just list the letters in [] and the search will try to match all of them. Lets see the code
SELECT firstname, lastname
FROM person
WHERE firstname LIKE ‘%[kv]%’
This will give the result
firstname lastname
Vincent America
Kofi 2smart4school
Lets say we want all people who have ‘k’ in their firstname and no ‘v’ in their firstname
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%[k^v]a%’
This will give the result
firstname lastname
Kofi 2smart4school
Ok, the last thing l’m going to talk about is the use of ESCAPE. Let’s now assume we put a new student whose last name is burger%.
INSERT INTO person
VALUES(3,’Man’,’ burger%’)
Now, lets say we want to find people who have ‘%’ in their last name. Because the data has the wild card symbol as part of it, you can not write simply like this
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%%%’
That will not work. To achieve this, we have to introduce another symbol of our choice and tell the query to escape our symbol but consider the following % as part of the data. So our query will look like this
SELECT firstname, lastname
FROM person
WHERE lastname LIKE ‘%$%%’ ESCAPE $
You can choose any symbol to use instead of $ as the escaping character.
This will give the result
firstname lastname
Man burger%
How can we specify range for character to return results for those names that lie in the range. Say 2nd character is in range a-g or x – z. We do it like this,
SELECT firstname, lastname
FROM person
Where LastName like ‘_[a-gx-z]%’
I hope this helps. Please comment, ask questions or send feedback on any information. I’ll be discussing Dynamic SQL and Variables in the next tutorial.
