In this guide, we are going to talk about how we can use wild card searches in SQL. Now if you've never heard of wildcard search don't worry, they're actually relatively straightforward. Once you have gotten in there and practice with them a little bit. I'm going to run a just a regular star query right here.

When I run this you can see I added a new blog post called “Another One of my Posts” and you'll see in a little while why I named that. What we are going to do with a wild card search is it gives us the ability to actually search through the string value inside of one of the columns. So let's actually walk there what this looks like.

So I'm going to say SELECT * FROM guides WHERE guides_title and then we add it “LIKE”. This is where we pass on our wild card. So here I'm going to say like in the percent sign and I'll just put %my% and let me run this query.

So what the little percent signs here are doing is these represent the start and endpoints for the wild card search. So whenever you need to find just a word that is contained somewhere inside of the string then you are going to put the percent sign. You should put the percent sign in at the start of the string and the end of the string after the “LIKE” query.

So what this is going to do is it's going to bring back any record that has the word “my” inside of it and that includes even when it doesn't start with the word my. Like the new one I created.

Now if I run this with case insensitive search and run that you can see that this still works just like before. It's also something important to keep in mind

Now, what happens when you want to filter this down a little bit? Well, you can say that you only want to find every single title that starts with “my” not that is included. In other words, this should return all the records except the guide with ID 6.

So now if I run this you can see that that one worked. We now have a limited data set. It is only bringing back the titles that have them at the very beginning. Now the same is true if I search for 'My' and remove the percent from the end.

This should return no values and that's exactly what we got because there was nothing in the database that ended with a “my” so this is very helpful. One of the most common ways that you will most likely use this is with the percent sign on each side. So this is a full wildcard search because it gives you the ability to simply find words in strings. This is an incredibly common thing that you're going to be doing when you're using SQL.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM guides;

SELECT *
FROM guides
WHERE guides_title LIKE '%My%';

SELECT *
FROM guides
WHERE guides_title NOT LIKE '%My%';