WHERE is an incredible method to narrow down results from a database based on our specified criteria. But what if we wanted to select records based on a pattern?
In that case we can use the LIKE operator in the WHERE clause.
Like Operator
The LIKE operator in WHERE allows you to match a pattern.
The syntax is as follows:
Select [Column Names | *]
Where [Column Name] Like [Criteria]
We can add additional options as well.
Select [Column Names | *]
Where [Column Name] Like [Criteria]
AND | OR [Column Name] [Operator] [Criteria]
We can see this in action inside SSMS.
Let's start off with a New Query. Remember to make sure the AdventureWorks database is selected on the dropdown menu, otherwise the query statements will not work as expected.
This time we will be using a different table than before, instead of the Person.Person table, we will be using the Production.Product table.
Let's see what kind of records are available to us in the Production.Product table. We can use a simple select all to get all the data from this table.
We get the result set of this table as expected. But let's say we want to see all the products that start with the letter A. In this case we can use a LIKE statement.
We construct this query by specifying the Column name in the Where clause and the A in the LIKE clause, since we want to match all the products that start with the letter A.
But we also want to match all the words that follow after A, to do this we put in a Wild-Card. A Wild-Card matches anything. How it will look is as follows:
For example
Select *
Where name Like 'A%'
The % is the Wild-Card character and it will match everything after A.
If we wanted, for instance to find all the products names that ended with an A, then we can move the % sign to the front and the A to the back.
What if we wanted to find a product name that has the word 'paint' in it and we do not care where in the product name 'paint' resides.
We could do this by adding the Wild-Card at the start of our criteria and at the end of our criteria.
This means that no matter what a paritcular word starts with and no matter what it ends with, it should have the word 'paint' in it.
Remember the query window is case-incensitive
Multiple values in Where Clause
We have discussed how to select values from a table, using a Where clause and a specifed value from a column.
For example: If we wanted to select a value in a table where the ID is 123.
Select *
From [Table Name]
Where ID = 123
If we wanted to select values where ID is 123 or 234 we would construct it as follows.
Select *
From [Table Name]
Where ID = 123
Or ID = 234
When we need to add in more OR statements then we just need to specify it as we did before and we can do this as many times as we prefer.
This is generally fine in a situation where we have a limited amount of values, two or maybe even ten values. But if we have hundreds of values for ID then it becomes cumbersome to manage and SQL becomes difficult to read and understand.
This is where another SQL clause comes into play, this clause is called IN. The syntax for using an IN query statement is similar and simple to what we are used to.
Select [Column Names | *]
From [Table Name]
Where ID in (criteria, criteria, criteria...)
For example:
Select [Column Names | *]
From [Table Name]
Where ID in (123, 434, 234, 233, 112, 431, 332...)
We enclose the criteria in parenthesis and separate them by a comma. We can see how this works in practice inside SSMS.
We are going to use the Person.Person table to demonstate IN.
Let's assume that we were given values from the sales department and they wanted to know the first name and last name of a particular group of people. The values they have provided are ID's.
The cumbersome way of performing this type of query would be as follows.
As we discussed we can perform the same query by using the IN query method.
As we can see the same result set is returned if we run both queries simultaneously.
If for instance you have to use sting values, then you need to enclose them in single quotes. For example we need to find the values for firstName. We would construct the query statement as follows:
If we want to use Date values, then we would also use single quotes.
Thats it! Well done on making it this far, we have seen how we can narrow down our result sets even further with Where and Like, so I hope you Liked this article. See you next time...
Top comments (0)