IN BETWEEN and LIKE Operators in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

IN BETWEEN and LIKE Operators in SQL Server with Examples

In this article, I am going to discuss IN BETWEEN and LIKE Operators in SQL Server with Examples. Please read our previous article where we discussed SQL Server Logical Operators with examples. SQL Server contains some special operators. Those are BETWEEN, NOT BETWEEN, IN, NOT IN, LIKE, NOT LIKE, EXISTS, SOME, ANY, ALL. In this article, we will discuss IN, NOT IN, Between, NOT Between, Like and NOT Like Operators and the rest of all other operators are going to be discussed in our next article.

Understanding IN BETWEEN and LIKE Operators in SQL Server:

Let us understand the IN, Between, and Like operators with examples. We are going to use the following Employee tables to understand these special operators.

Please use the below SQL Script to create database EmployeeDB, Employee table and populate Employee table with test data.

--Create database EmployeeDB
Create database EmployeeDB
Go

Use EmployeeDB
Go

-- Create Employee table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some test data into Employee table
INSERT INTO Employee VALUES('Pranaya','Pranaya@g.com','Male', 'IT', 25000, 30,'Mumbai')
INSERT INTO Employee VALUES('Tarun','Tarun@g.com','Male', 'Payroll', 30000, 27,'Odisha')
INSERT INTO Employee VALUES('Priyanka','Priyanka@g.com','Female', 'IT', 27000, 25,'Bangalore')
INSERT INTO Employee VALUES('Preety','Preety@g.com','Female', 'HR', 35000, 26,'Bangalore')
INSERT INTO Employee VALUES('Ramesh','Ramesh@g.com','Male','IT', 26000, 27,'Mumbai')
INSERT INTO Employee VALUES('Pramod','Pramod@g.com','Male','HR', 29000, 28,'Odisha')
INSERT INTO Employee VALUES('Anurag','Anurag@g.com','Male', 'Payroll', 27000, 26,'Odisha')
INSERT INTO Employee VALUES('Hina','Hina@g.com','Female','HR', 26000, 30,'Mumbai')
INSERT INTO Employee VALUES('Sambit','Sambit@g.com','Male','Payroll', 30000, 25,'Odisha')
INSERT INTO Employee VALUES('Manoj','Manoj@g.com','Male','HR', 30000, 28,'Odisha')
INSERT INTO Employee VALUES('Sara',' Sara@g.com','Female', 'Payroll', 28000, 27,'Mumbai')
INSERT INTO Employee VALUES('Lima','Lima@g.com','Female','HR', 30000, 30,'Bangalore')
INSERT INTO Employee VALUES('Dipak','Dipak@g.com','Male','Payroll', 32000, 25,'Bangalore')
GO

BETWEEN Operator in SQL Server:

The BETWEEN operator in SQL Server is used to get the values within a range. Generally, we use the BETWEEN operator in the WHERE clause to get values within a specified range. For example, if you want to fetch all the employees between ID 3 and 7 from the Employee table, then you need to use the BETWEEN operator as shown below. 

SELECT * FROM Employee WHERE ID BETWEEN 3 AND 7

The above SQL statement will return records from the Employee table where the employee ID is between 3 and 7. Following is the output of the above SQL query.

Points to Remember while working with Between Operator in SQL Server:

1. Between Operator returns true if the operand is within a range.
2. Between Operator will return records including the starting and ending values.
3. This operator support only the AND operator.
4. The BETWEEN Operator takes the values from small to big range in the query.

NOT BETWEEN Operator in SQL Server:

In a SQL Server, the BETWEEN operator will return records within a range including the values specified in the Between Operator. But if you use the NOT keyword along with the BETWEEN operator then it will return data where the column values not in between the range values. For example, the following SQL Query will return employees whose employee id not between 3 and 7. The point that you need to remember is it also not return the employee whose id is 3 and 7. 

SELECT * FROM Employee WHERE ID NOT BETWEEN 3 AND 7

Following is the output of the above SQL query.

IN Operator in SQL Server:

The IN Operator in SQL Server is used to search for specified values that match any value in the set of multiple values it accepts. Generally, we will use this IN operator in WHERE clause to compare column or variable values with a set of multiple values. For example, if you want to fetch all the employees whose department is either IT or HR, then you could write the SQL Query using the IN Operator as shown below.

SELECT * FROM Employee WHERE Department IN (‘IT’, ‘HR’)

Following is the output of the above SQL query.

NOT IN Operator in SQL Server:

This is just opposite to the IN Operator. The IN operator takes a set of values and then returns the records whose column values matched with the values it has.  But if you use the NOT keyword along with the IN operator, then it will return data where column value not in the set of values. For example, the following SQL query will return all the employees where the Department not in IT and HR.

SELECT * FROM Employee WHERE Department NOT IN (‘IT’, ‘HR’)

Following is the output of the above SQL query.

LIKE Operator in SQL Server:

This is one of the most frequently used operators in SQL Server. The LIKE operator in SQL Server is used to search for character string with the specified pattern using wildcards in the column. In SQL Server, pattern means its specific string of characters with wildcards to search for matched expressions. Generally, we will use this LIKE operator in the WHERE clause. Here, we will try to understand the LIKE operator using different types of wildcard characters.

Using ‘%’ wildcard

Example1:

The following SQL query will return all employees whose name starts with the character ‘P’ followed by any string of characters. This is because here we mentioned a pattern like ‘P%’. Here ‘%’ is a wildcard character that we will use before or after characters to search for the required matched string of characters.

SELECT * FROM Employee WHERE Name LIKE ‘P%’

Following is the output of the above SQL query.

Example2:

The following SQL query will return all employees whose name ends with the character ‘a’. This is because here we mentioned the pattern as ‘%a’. This means return all records whose name ends with the character ‘a’.

SELECT * FROM Employee WHERE Name LIKE ‘%a’

Following is the output of the above SQL query.

Example3

The following SQL query will return all employees with the Name containing the word ‘am’ anywhere in the name column because we mentioned patterns like ‘%am%’. This means it will check for the respective word anywhere in the column irrespective of characters in front or back.

SELECT * FROM Employee WHERE Name LIKE ‘%am%’

Following is the output of the above SQL query.

Understanding the WildCard Characters:

You can use the following wildcard characters with the LIKE operator in SQL Server.

1. % symbol represents any no of characters in the expression.
2. _ will represent a single character in the expression.
3. The [] symbol indicates a set of characters in the expression.
4. [^] will represent any single character, not within the specified range

Examples:

We already saw some examples of the % wildcard character. Let us see some examples of other wildcard characters.

WAQ to display employee details whose name contains 3 characters.
SELECT * FROM Employee WHERE Name LIKE ‘___’

WAQ to display employee details whose name contains ‘A’ character.
SELECT  * FROM Employee WHERE Name LIKE’%A%’

WAQ to display employee details whose name starts with ‘P’ character and ends with ‘A’ character.
SELECT * FROM Employee WHERE Name LIKE ‘P%A’

WAQ to display employee details whose name starts with J, H, K, U characters.
SELECT * FROM Employee WHERE Name LIKE'[J, H, K, U]%’

WAQ to display employee details whose names start with A to Z characters.
SELECT * FROM Employee WHERE Name LIKE'[A-Z]%’

WAQ to display employee details whose name not start with A to Z characters.
SELECT * FROM Employee WHERE Name NOT LIKE'[A-Z]%’

In the next article, I am going to discuss ALL Operator in SQL Server with Examples. Here, in this article, I try to explain the IN BETWEEN and LIKE Operators in SQL Server with Examples. I hope you enjoy this IN BETWEEN and LIKE Operators in SQL Server with Examples article. I would like to have your feedback. Please post your feedback, question, or comments about this IN BETWEEN and LIKE Operators in SQL Server with Examples article.