Big SQL Energy Beginner Course: Lesson 14 Like vs. ilike & Wildcard Variations

Jan 14, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 14 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about ilike, like, and Wildcard variations in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 14: Like vs. ilike & Wildcard Variations

🎥 Video

Remember in the previous lesson where we learned the basics of wildcards with the like operator? Don’t forget that the like operator is CASE SENSITIVE when comparing strings, so the following 2 queries will produce different results:

select 
   *
from 
   orders
where
   customer_first_name like 'D%' ;

select 
   *
from 
   orders
where
   customer_first_name like 'd%' ;

In this lesson, we’re going to learn about different variations of wildcards and how we can further customize our patterns with wildcards to pull even more specific results.

The % symbol represents 0 or more characters in string pattern which means that it can sometimes take the place of nothing or many letters. What if we want to be even more specific and restrict our search to only exactly 1 letter? Or exactly 3 letters? No less and no more. This is where the _ symbol comes into play! The _ symbol takes the place of exactly 1 character, no less and no more! So it can be a great way to refine your search if you’re looking for a pattern of a specific length.

The pattern below would pull all customers with a first name starting with J followed by exactly 3 letters. So Jane and John would be returned, but not Johnny or Joe!

*** Note: NO spaces are needed between the _ symbols, but I had to add spaces in the query below to separate & emphasize the 3 different underscores for clarity***

select 
   *
from 
   orders
where
   customer_first_name like 'J_ _ _' ;

If you’re looking for something hyper-specific, you can even mix and match % and _ to pull EXACTLY what you’re looking for. This query pulls all customers with a first name with the second letter o:

select 
   *
from 
   orders
where
   customer_first_name like '_o%' ;

On top of customizing our wildcard searches by restricting the number of characters with _, we can also switch our pattern matchings from being case sensitive with LIKE to not case sensitive with ILIKE. Yes, I know this sounds made up, but if you literally throw in an I before LIKE, it magically makes it not case sensitive. It’s really that easy!

So all the queries below will pull Jane & John even though they have a capital J at the start of their first names. The first 2 queries will also pull any name starting with J since the % is not restrictive on the number of characters! But the last query will only pull 4 letter names starting with J.

select 
   *
from 
   orders
where
   customer_first_name ilike 'J%' ;

select 
   *
from 
   orders
where
   customer_first_name ilike 'j%' ;

select 
   *
from 
   orders
where
   customer_first_name ilike 'j_ _ _' ;

Wildcards can be… well, WILD! But I hope I made them a little easier and more fun for you today!

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!