Search by how it starts or ends

01 / 06

Where the `%` sits matters

Last lesson you wrote '%Bread%', with one on each side. But a % is allowed on one side only.

Move it and you get "starts with" and "ends with" instead.

02 / 06

'Lane%' says "starts with Lane, and after that anything goes". There is no % in front, so the head has to line up exactly.

SELECT name FROM shops  WHERE name LIKE 'Lane%';

Result

Lane Bookshop
Lane Cafe

03 / 06

The other way round, '%Bookshop' means "ends with Bookshop". No % behind it, so the tail has to line up exactly.

Same word, different meaning, just from where you put the %.

SELECT name FROM shops  WHERE name LIKE '%Bookshop';

Result

Lane Bookshop
Sunny Bookshop

04 / 06

The one to watch is leaving the % off altogether. LIKE 'Lane' does not mean "starts with Lane", it means "is exactly Lane".

Which is much the same as =. When a search brings back nothing at all, the first thing to check is whether you wrote the %.

SELECT name FROM shops  WHERE name LIKE 'Lane';

05 / 06

Put the three side by side and the difference is plain.

'Lane%' … starts with. Search on the head of a name (a register, a product code)
'%Bookshop' … ends with. Search on the tail (a file extension, a domain)
'%Bread%' … contains. Anywhere will do (an ordinary search)

When in doubt, contains is fine. Just know that the wider you open it, the more junk it drags in.

06 / 06

Put NOT in front of LIKE and back come the rows that do not match. "The shops that do not have bread in the name", for instance.

Searching for something and leaving something out — once you can write both, you can walk straight up to the data you want. Let us try it.

SELECT name FROM shops  WHERE name NOT LIKE '%Bread%';

Result

Lane Bookshop
Anchor Flower Store
Sunny Bookshop
Lane Cafe