SQL LIKE
LIKE tests whether text matches a pattern. A pattern can stand for part of the text.
A shared start
The % sign stands for the rest of the name. Only one name starts with the letter A.
SELECT name FROM customers WHERE name LIKE 'A%' ORDER BY name;A run in the middle
Two % signs hold a fixed middle part. Only one product name holds the letters tt in a row.
SELECT name FROM products WHERE name LIKE '%tt%' ORDER BY name;Exactly one character
The _ sign stands for one character and no more. Both Austin customers pass, with the last letter left open.
SELECT name, city FROM customers WHERE city LIKE 'Austi_' ORDER BY name;Example
SELECT name FROM products WHERE name LIKE 'M%' OR name LIKE '%p' ORDER BY name;Key points
- LIKE: a test that matches text against a pattern
- %: a pattern sign that stands for any run of characters
- _: a pattern sign that stands for exactly one character
- Use LIKE in a WHERE clause to find text with a shared start, end or middle.
In this example, the first pattern keeps names that start with M. The second pattern keeps names that end with p. OR joins the two patterns, so two product names pass.
Note
SQLite matches LIKE without case for plain letters. Other engines may treat upper and lower case as different.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
Which pattern matches a name that starts with B, with any ending after it?