How do I use the SQL WHERE clause with text?
The SQL WHERE clause allows you to filter rows based on a criteria you define. This lesson will focus specifically on filtering rows based on text values. Were we to find all rows in the Person.Person table where the FirstName contains the word “Michael”, we would type:
WHERE FirstName = 'Michael'
Notice the equals comparison operator after the WHERE and before the filtering text in the WHERE clause. Every WHERE clause must include one of several possible comparison operators. See our lesson on SQL Comparison Operators that will be coming up soon for the complete list of operators you can use.
We can use the operators “AND” and “OR” to add more conditions to our WHERE clause. To find all rows from the Person.Person table with a FirstName value equal to “Michael” and a LastName value equal to “Allen”, we can type:
WHERE FirstName = 'Michael' AND LastName = 'Allen'
We could use the “OR” operator to find all rows of Person.Person with a FirstName equal to “Michael” or equal to “James”:
WHERE FirstName = 'Michael' OR FirstName = 'James'
We can find all rows of Production.Product where the Name column starts with a letter less than “K” with the query:
WHERE Name < 'K'
There are several other ways to filter rows of data based on text values in the SQL WHERE clause. Later sections will discuss other comparison operators and ways to filter text data. Complete the practice problems below to gain a bit more experience with the techniques learned in this lesson:
- Find all rows in Person.Person where the FirstName value equals “Steve”.
- Find all rows in Production.Product where the Color column is “Red”.
- Find all rows in Production.Product where the Color column is either “Red” or “Black”.