Imagine you have thousands of customers. You want to get only the phone number of customers whose last name is “Doe”.
The syntax to do that is the following:
In that query:
- the condition is: where last_name=’Doe’.
- only rows for which the value of column last_name equals “Doe” will be displayed.
I don’t remember the exact value of the customer’s name. I just remember it starts with “ra”. What should I do?
In that case, the syntax to find your customer is the following:
This will display a list of customers which name starts with “ra”.
Hopefully, looking at the names displayed, you will remember which customer you were looking for.
Can I use something else than “ra”?
Yes, absolutely. You can use any group of letters, numbers and special characters of any size instead of “ra”.
What if I remember only the last letters of the name?
Put the sign “%” before the letters you remember. Example: we remember that the customer’s last name ends with “ao”. Then the syntax is the following:
This will display a list of customers which name ending with “ao”.
I just remember a few letters contained in the name: “ao”. I don’t remember if it is in the beginning, in the middle or in the end.
In that case, you put a “%” before and after the letters you remember:
Example of names that would match (they all contain “ao”):
I want to display two clients in one query, one with the name ‘rao’ and another with the name ‘matao’
In that case, use the keyword “in” followed by a list of values. The where condition will be fulfilled if the value of the column is equal to one of these values. Here is the syntax if you want to display these two clients’ phone number:
I remember the first and second character of the name but I don’t remember the second one.
In that case you use the following syntax:
"_" can be any character, but can be only one character. As the result:
- following will be found : rao, riot
- following will not be found: riao
- Column value is equal to … : =
- column value is strictly greater than (cannot be equal) … : >
- column value is strictly smaller than (cannot be equal) … : <
- column value is greater or equal to … : >=
- column value is smaller or equal to … : <=
- column value is between x and y (x and y are numbers): between x and y
- column value not equal to x: <> x
In this example, you knew the first and second character (one unknown character in between). If you know the first and third character (two unknown character in between), then you would just need to use the underscore ("_") twice:
What if the condition uses numbers instead of text?
In case the value of the column used in the condition is a number, you can use the following operators:
Example 1: we want to show salesmen for which the total sales is equal to or greater than 100 000 dollars. The syntax is the following:
Example 2: we want to show salesmen for which the total sales is strictly smaller than 100 000 dollars (can be less than 100 000 but cannot be equal to 100 000). The syntax is the following: