SQL Exercise: Let’s practice Filtering with Wildcards

In this exercise, I will be using AdventureWorks 2008 Database and will write my SQL queries using SSMS 18. All the exercises are from the book: Beginning T-SQL 2008

---

---

Q1: Write a query that displays the product ID and name for each product from the Production.Product table with the name starting with Chain.

SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE 'Chain%';

Q2: Write a query like the one in question 1 that displays the products with helmet in the name.

SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '%helmet%';

Q3: Change the last query so that the products without helmet in the name are displayed.

SELECT ProductID, Name
FROM Production.Product
WHERE Name NOT LIKE '%helmet%';

Q4: Write a query that displays the business entity ID number, first name, middle name, and last name from the Person.Person table for only those rows that have E or B stored in the middle name column.

We could write the query in multiple ways:

• Using LIKE wildcard. It is a logical operator that determines if a character string matches a specified pattern.
• Using OR and combining multiple conditions

SELECT BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
WHERE MiddleName LIKE '[E, B]';

SELECT BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
WHERE MiddleName = 'E' OR MiddleName = 'B';

Q5: Explain the difference between the following two queries:

SELECT FirstName
FROM Person.Person
WHERE LastName LIKE ‘Ja%es’;

SELECT FirstName
FROM Person.Person
WHERE LastName LIKE ‘Ja_es’;

The first query will return rows with any number of characters replacing the percent sign. The second query will allow only one character to replace the underscore character.

---

I will be adding more examples so keep an eye out. Also, feel free to drop in your suggestions and questions.