Essential Question: How can I do a search in a SQL Table when I am not sure how to spell the word I am searching?
Mastery Objectives:
SWBAT use the like command to perform complex SQL queries when they know partial data.
Directions: There are times when you want to search a SQL Table but may not know the exact spelling of a word or the case of the letters. There is a way to do a search for part of a word or words that begin with a letter or end with a letter. Here are all the different formats for searching for part of a word or string:
The LIKE command is used in a WHERE clause to search for a specified string in a field.
There are two wildcards often used in conjunction with the LIKE command:
% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
Assignment: Try the following queries to search the table using the SQL Editor and the Customers table: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_like and take a screenshot and paste it into google docs. Write a description of your results. Submit your google doc to google classroom.
SELECT * FROM Customers WHERE ContactName LIKE 'j%';
SELECT * FROM Customers WHERE ContactName LIKE '%g';
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
SELECT * FROM Customers WHERE CustomerName LIKE '_a%';
SELECT * FROM Customers WHERE Address LIKE '1_%';
SELECT * FROM Customers WHERE Address LIKE '5%e';
Create 4 queries using LIKE on your own.