SQL WHERE Syntax: Filtering Data Effectively

SQL Wildcard

A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Example:

SELECT column_Name1, column_Name2 …., column_NameN FROM table_Name WHERE column_name LIKE pattern:

Let’s take the following Employee table:

Return all customers starting with either “b”, “s”, or “p”:

SELECT * FROM Employee WHERE Name LIKE ‘[rsv]%’;

The [] wildcard returns a result if any of the characters inside gets a match.

Output:

Let’s take the following Student table:

Return all customers starting with “a”, “b”, “c”, “d”, “e”, “f”, “g”, “h”, “i”, “j” or “k”:

SELECT * FROM Name WHERE Name LIKE ‘[a-k]%’;

Output:

The – wildcard allows you to specify a range of characters inside the [] wildcard.

Course Video

NOTE: Practice below questions on site editor.

1. Write SQL query to get Firstname, Lastname, from Person_Person table where firstname starts with ‘b’.

Output:

2. Write SQL query to get addressline1, addressline2, city from person.address table where city start with ’M_ N’ .

Output:

3. Write SQL query to get productid, name, color, listprice from Production_Product table where productname start with ‘Adjustable Race’.

Output:

4. Find all vendor whose name contain exactly 5

Table name: (Purchasing_Vendor)

Output:

5. Write a query to fetch the city name start with ‘p’.

Table name:(Person_Address)

Output:

6. Find all departments whose Name contains the substring ‘ion’ and whose GroupName contains the substring ‘ing’ from HumanResources_Department table.

7. Write an SQL query to find all address types whose Name starts with the letter ‘S’ and the rowguid contains the digit ‘9’ at any position from Person_AddressType table.