Filter Records by a Character Field

In this video segment, we’re going to discuss how to filter records by using a character field. So let’s get straight to that demonstration.

So as you can see, I’ve already opened up our SQL server Management Studio and in the query editor window I’ve written a SELECT statement to select the BusinessEntityID field, the FirstName field and the LastName field from the Person.Person table. I’ve gone ahead and executed this query, and it has returned 19,972 rows or records. That’s how many people are in our Person.Person table.

Now in this demonstration, we want to retrieve just specific people, either by the FirstName or LastName fields or the character fields. So we’ll start off writing a WHERE statement or adding a WHERE clause to our SELECT statement, and we’re going to look where the LastName, LastName equals Adams. We have several Adams in our table. And any time I want to look for literal text, I need to make sure I put the single quotes around that literal text. So there you can see that I am going to retrieve the data where the LastName equals Adams. So let’s go ahead and execute that query, and I returned 86 rows. So there were 86 people whose last name was Adams.

So there’s an Aaron Adams, Adam Adams, Alex Adams, Alexander Adams. So 86 people. Now what if I wanted to filter that down a little bit more? I could also include an AND, AND, AND predicate, and let’s say I wanted the FirstName, FirstName that was Alex. We’ll find Alex there. And the FirstName equals Alex. So Alex, and we’re good. So we’re going to execute. So there’s that one person whose first name was Alex and their last name was Adams.

Now what if I wanted to just find all the people whose last name was Adams and their first name started with the letter A? We can actually use wildcards. I’m going to make a slight error here just to show what happens if we mistype. So I’m going to type in where FirstName equals A. I’ll type in a percent sign. The percent sign will be our wildcard. I have another single quote, so in this query I’m typing in where LastName equals Adams and first name equals A%. So when I execute this, it comes back with nothing, because of that slight little error I told you I was going to make. Because when it came back, we knew that there were 86 people where the LastName equals Adams. So we know that’s not the problem. But in the second part of the query, I put in where the FirstName equals A%. So it looked for anybody whose first name was A%. We didn’t have anybody whose name was A%.

So to activate the wildcard, we need to actually change this equal into a LIKE. The LIKE statement is what activates the wildcards, to turn on the wildcards, so that percent sign is not a percent sign, it’s a wildcard now. So now when I go ahead and execute this query again, you see we have all the people whose last name is Adams and their first name starts with the letter A.

Is there other wildcards that we can use? Absolutely. What if I wanted to find everybody whose first name, the second letter was an A? So I can use a, within my parameters there, I can put an underscore. An underscore is a single placeholder. So a single placeholder so the first value can be of any value, but the second letter is going to be an A and the percent sign would have any characters after the A. So if I go an execute this again, you can see we have still all the Adams, we’ve got 86 people whose last name is Adams, but now we’ve got apparently there are 33 people whose the second letter of their name is an A. Not their first letter, but the second letter of their first name was an A. So Aaron Adams, Bailey Adams, Carla. You see the first letter could have been any character, but the second character is an A.

So in this video segment, we have seen how to filter records by using a character field. Thank you for watching our video series.