IN BETWEEN and LIKE Operators

IN BETWEEN and LIKE Operators in SQL Server

In this article, I am going to discuss IN BETWEEN and LIKE Operators in SQL Server. 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.

IN BETWEEN and LIKE Operators in SQL Server

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 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.

BETWEEN Operator in SQL Server

Points to Remember:
  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.

NOT BETWEEN Operator in SQL Server

IN Operator in SQL Server:

The IN Operator in SQL Server is used to search for specified values matches 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.

IN Operator in SQL Server

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.

NOT IN Operator in SQL Server

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 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.

Using'%' wildcard

Example2:

The following SQL query will return all employees whise 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.

LIKE Operator in SQL Server

Example3

The following SQL query will return all employees with 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 column irrespective of characters in front or back.

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

Following is the output of the above SQL query.

IN BETWEEN and LIKE Operators - LIKE

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 wildacrd 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 step by step with some examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *