Lesson 4 of 10
Pattern matching
- 11 min
- 5 checks
- 3 hints
- Go to task
Goal
LIKE with % and _
Explanation
LIKE compares a value to a pattern instead of an exact value. The % symbol means any number of characters, even zero, and _ means exactly one character. So the pattern '05%' matches anything that starts with 05, whatever its length, and '05__' matches anything that starts with 05 and is exactly four characters long.
In SQLite, LIKE ignores case for ASCII letters, so 'Khalid@Mail.SA' matches the pattern '%@mail.sa'. =, on the other hand, is case-sensitive, and in PostgreSQL even LIKE is, so don't rely on this behavior if your query will run on another database.
A loose pattern can catch things you don't want: '%mail.sa' also matches webmail.sa. So make the pattern pin down the domain exactly, like '%@mail.sa'.
Your task
Show the name, email and phone of the customers whose email is on the mail.sa domain and whose phone number is complete, meaning it starts with 05 and is exactly ten digits long.
Checks
5 checks- Columns are name, email and phone
- Every email is on the mail.sa domain
- No one was dropped because of capital letters
- The phone number is ten digits starting with 05
- No customer is missing
Tables in this lesson
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
phone TEXT
);
INSERT INTO customers (name, email, phone) VALUES
('Layan', 'layan@mail.sa', '0551234567'),
('Khalid', 'Khalid@Mail.SA', '0509876543'),
('Reem', NULL, '0543216789'),
('Sara', 'sara@gmail.com', NULL),
('Fahad', NULL, NULL),
('Noura', 'noura@mail.sa', '055123456'),
('Omar', 'omar@webmail.sa', '0561112233'),
('Hind', 'hind@mail.sa', '0598765432'),
('Yousef', NULL, '0533334444');