Section 3 — String WHERE Operators

How do you compare an exact text value in WHERE?

Use =. Example: WHERE State = 'Utah'.

How do you exclude an exact text value?

Use != or <>. Example: WHERE State != 'Utah'.

What does <> mean in SQL?

Not equal to; it works like != in many DBMS engines.

What does ILIKE do?

Performs a case-insensitive text comparison. Example: WHERE Name ILIKE 'isla'.

What does NOT ILIKE do?

Excludes text case-insensitively. Example: WHERE Name NOT ILIKE 'imani'.

LIKE vs. ILIKE

LIKE is case-sensitive when the DBMS treats case as significant; ILIKE is case-insensitive.

What does % mean in a LIKE condition?

A wildcard representing zero or more characters.

Example: names beginning with Fred

WHERE Name LIKE 'Fred%'

What names can LIKE 'Fred%' match?

Fred, Freddy, Freda, or any value that starts with Fred.

How do you find names ending in “son”?

WHERE Name LIKE '%son'

How do you find text containing “Lake”?

WHERE City LIKE '%Lake%'

What does IN (…) do with strings?

Matches a text value in a list. Example: WHERE State IN ('Utah', 'Nevada').

Can you use wildcards inside IN (…)?

No. Use LIKE for wildcard matching.

Example: customers in Utah or Nevada

SELECT Name, State FROM Customer WHERE State IN ('Utah', 'Nevada');

Example: customers not in Utah or Nevada

SELECT Name, State FROM Customer WHERE State NOT IN ('Utah', 'Nevada');

What quotation marks should surround text values in standard SQL?

Single quotes. Example: WHERE State = 'Utah'.