Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases SQL Basics Finding the Data You Want Finding Data that Matches a Pattern

Ryan Quinn
Ryan Quinn
2,027 Points
Posted by Ryan Quinn
Ryan Quinn
2,027 Points

When using the % as a wildcard, what is the difference in placement?

What is the difference between "%drew", "Alien%", and "%Brief History%" ?

2 Answers

Lindsay Sauer
Lindsay Sauer
12,029 Points

Hi Ryan,

Jonathan is right about the first two searches, however %Andrew% would search for all records that contain Andrew, but does not have to match "Andrew" exactly.

% in a SQL query means "match 0 or more characters". So the following query:

SELECT last_name FROM users WHERE last_name LIKE "%Andrew%"

Would match each of these:

  • Andrew

  • Landrew

  • Andrew*s*

  • Andrew*son*

In a SQL query where you search for something like "%drew", with the wildcard at the start of the value you are searching for, it will match records with zero or more characters in front of "drew", but it must end with "drew" because there is no wildcard at the end.

In a SQL query where you search for something like "Alien%", with the wildcard at the end of the value you are searching for, it will match records with zero or more characters after "Alien", but it must start with "Alien" because there is no wildcard at the start.

Ryan Quinn
Ryan Quinn
2,027 Points
Ryan Quinn
Ryan Quinn
2,027 Points

I understand now. Thank you so much.

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

It might not match "Landrew", if the comparison was case-sensitive (it is in some databases, not in others).

Lindsay Sauer
Lindsay Sauer
12,029 Points

Yes Steven, good point, but for this example it's okay to assume we're using a case-insensitive, popular database such as MySQL.

We're just discussing the basics, and if you're working with a specific database like Oracle you should probably have already learned that, to do a case-insensitive search, you'd have to force the case.

MD MONIRUZZAMAN
MD MONIRUZZAMAN
6,130 Points
MD MONIRUZZAMAN
MD MONIRUZZAMAN
6,130 Points

Very precisely explained.Thanks a lot!!

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse Moderator 91,253 Points
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points

As I understand it, it makes a difference as to which part of a string is searched.

If for example you were to search for "%drew" in a LIKE clause you're searching for records that end in drew.

If you searched ""Alien%" you'd be looking for records that began with the string Alien.

But %Andrew"% would match all records that matched exactly that string. So for example all users of a website that had a first_name of Andrew.

Hope this helps :)

Ryan Quinn
Ryan Quinn
2,027 Points
Ryan Quinn
Ryan Quinn
2,027 Points

That helps. Thank you very much.