WHERE with LIKE pattern matching in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL LIKE operator do in a WHERE clause?

The LIKE operator is used to search for a specified pattern in a column. It helps filter rows where the column's value matches the pattern.

beginner

What wildcard characters are commonly used with LIKE in SQL?

The two main wildcards are:<br>- % which matches zero or more characters<br>- _ which matches exactly one character

beginner

Write a simple SQL query to find all customers whose name starts with 'A'.

Example query:<br>SELECT * FROM customers WHERE name LIKE 'A%';<br>This finds all names beginning with 'A'.

intermediate

How does LIKE '%abc%' differ from LIKE 'abc%'?

LIKE '%abc%' finds any value containing 'abc' anywhere.<br>LIKE 'abc%' finds values starting with 'abc'.

intermediate

Can LIKE be case sensitive?

It depends on the database system and collation settings.<br>For example, in MySQL, LIKE is case-insensitive by default, but in PostgreSQL it is case-sensitive.

Which wildcard matches exactly one character in a SQL LIKE pattern?

A _

B *

C %

D #

The underscore (_) matches exactly one character in a pattern.

What does the pattern '%book%' match in a LIKE clause?

A Values containing 'book' anywhere

B Values ending with 'book'

C Values starting with 'book'

D Values exactly equal to 'book'

The percent signs (%) before and after 'book' mean it can appear anywhere in the value.

Which SQL query finds rows where the column 'city' starts with 'New'?

A SELECT * FROM table WHERE city LIKE '%New';

B SELECT * FROM table WHERE city LIKE 'New%';

C SELECT * FROM table WHERE city LIKE '_New%';

D SELECT * FROM table WHERE city LIKE '%New%';

The pattern 'New%' matches values starting with 'New'.

If you want to find all rows where a column contains exactly 3 characters, which pattern would you use?

A LIKE '%'

B LIKE '___%'

C LIKE '___'

D LIKE '_'

Three underscores (_) mean exactly three characters.

In which case is LIKE case-sensitive by default?

A MySQL

B Oracle

C SQLite

D PostgreSQL

PostgreSQL's LIKE operator is case-sensitive by default.

Explain how the SQL LIKE operator works with wildcards in a WHERE clause.

Think about how you search for words containing or starting with certain letters.

Describe a scenario where you would use LIKE '%abc%' versus LIKE 'abc%' in a query.

Consider searching for a substring anywhere or only at the beginning.