Module 2: How to Filter Data with WHERE Clause - Part 2

Previous Tutorial - Next Tutorial

Let’s start with another operator which is the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specific string pattern in a column. So the percentage and underscore are the two wildcard characters using which you can search for a specific string pattern in conjunction with the like operator.

So LIKE operator is used to finding those records that will start with some particular string or end with some particular string or contain some particular string. Now in this person table, I want to find that person whose last name starts with the Walt word. So for that, I need to write the WHERE clause something like (WHERE LastName LIKE 'walt%') So now if I execute this query, I will get that person whose last name started with the Walt for example Walters, Walton, etc?

Now I want to find that person whose first name ends with the word VIN and I need to write WHERE clause something like (WHERE LastName LIKE '%vin'). Now if I execute this query, I will get the person whose last name ends with a VIN word. So for example Devin, Kevin. Kelvin etc.

So in LIKE operator to find the start with character string you need to specify this wildcard character in the end and to find those records that end with some particular character string. So for that, you need to define this wildcard character in the starting. Now in the next requirement, I want to find that person whose last name contains the word 'REN'. So the REN word occurs anywhere in the name so it can occur in the starting, It can occur at the end of it may be present in the middle of this name.

So let's see how we can use the LIKE operator to find the records for that last name. So if I execute this query I will get the person whose last name contains REN. So it can be anywhere. It can be in the middle. It can be in the starting and it can be in the End.

This is straightforward but sometimes you are in a situation where you want to find wildcard characters like percentage and underscored from the column value itself. So to find these wildcard characters from the column value is not very straightforward which you might think but SQL server provides a way to search for wildcard characters.

We can search for it using the escape option. Let's see how we can use the ESCAPE option. So to demonstrate that ESCAPE option I need to use another table which is a product photo table. So let me open a new query window. Now in this query, I want to search for those rows in which this large photo file name column has the character green underscore but not is that underscored is a wildcard character.

Without spacing the escape option the query would search for any description value that contains the word green followed by any single character other than the underscore. So if I execute this query, we will get the occurrence of green as well as any single character in this record. So now to skip these two (result sl 2 & 6) records I need to redefine this query and use the ESCAPE option. Let's see how we can use the ESCAPE option (WHERE LargePhotoFileName LIKE ‘%green!_%’ ESCAPE ‘!’) and execute this query. Two records are now discarded.

Now this time I'm getting only those records whose value contains a green underscore. Now let's move on to the next operator which is IN operator. Now the IN operator allows you to specify multiple values in a WHERE clause. You can see that IN operator is a shorthand for multiple or conditions.

Let's see the IN operator in action. Now in this query, I want to find that person whose title is either Senior or Mrs. Let's see how we can use the IN operator to find those records. So for that, I need to write WHERE clause, and when I execute the query I will get only those people whose title is either Senior or Mrs.

Now, this query can be rewritten using the OR operator. Now when I execute both queries then I will get the same record. So similarly you can use this not IN operator in this query. For example, If I write not an operator and execute this query then I will get that person whose title not is either Senior or Mrs.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions