In a previous post, we discussed how to write a select statement. Over the next several posts we will discuss how to use the WHERE keyword to filter records by numbers, character data, dates and times, and NULL values. This post specifically will focus on filtering records by character data.

Finding Character Data

SELECT ProductID, Name, ProductNumber,
	Color, StandardCost, ListPrice
FROM Production.Product
WHERE Name = 'Blade'

In the code above, we have added a WHERE keyword to find all the records that have the word ‘Blade’ in the Name column. Make sure to use single quotes around the text you wish to filter on. (Note: The NAME column is displayed in blue because it is also a T-SQL keyword. As a database designer it is bad practice to name columns or tables the same as keywords.)

In the result below, you will notice that only one record has been returned from the Production.Product table.

Finding Data Beginning with a Specific Letter

If we wanted to find all the products that started with the letter ‘B’ we would need to use the LIKE keyword along with the ‘%’ keyword. If you use the = sign instead of the LIKE keyword no records will be returned, because there is no product named ‘B%’.

SELECT ProductID, Name, ProductNumber,
	Color, StandardCost, ListPrice
FROM Production.Product
WHERE Name LIKE 'B%'

After we run this code, four records are returned with each record starting with the letter ‘B’ and an unlimited number of characters after the ‘B’. This is because the LIKE predicate activates the ‘%’ wildcard.

Additional Wildcards to Filter Character Data

We could further filter these results by using an underscore ‘_’ to be a single character placeholder. As an example, if we wanted to find all products that started with the letter ‘B’ and the third character needs to be an ‘a’, we would write the following code.

SELECT ProductID, Name, ProductNumber,
	Color, StandardCost, ListPrice
FROM Production.Product
WHERE Name LIKE 'B_a%'

The results of this code will return two records where the first letter is a ‘B’, while the second character can be any letter and the third letter must be an ‘a’.

(Note: In the case of the AdventureWorks database, the search results are not case-sensitive. Each database as its own collation setting that determines whether a search is case-sensitive or not.)