Remembering the Percent as Wildcard

It’s been a bit since I was typing SQL into a REPL. One interface that didn’t come back to me right away is the percent character in a like or ilike query:

select full_name from users where full_name ilike 'jake%';

Returns:

full_name
-----------
Jake Gyllenhaal
Jake Tapper
Jake Worth
(3 rows)

This percent sign ensures we get all the Jakes. Here’s how I’m going to remember this next time.

Like everything SQL, this an ISO/ANSI standard and there’s a long lost history. If you think like me, and you might wonder why this character is not an asterisk. Here’s my new mental model to reinforce the percent sign.

First, the asterisk has a mathematical meaning already in programming. In many languages, 2 * 2 = 4.

Second, and more importantly, in the SQL standard, asterisk already means “all columns”, so it was taken.

That’s we use percent!