IN BETWEEN and LIKE Operators

IN BETWEEN and LIKE Operators in SQL Server

SQL Server contains some special operators. Those are BETWEEN, NOT BETWEEN, IN, NOT IN, LIKE, NOT LIKE, EXISTS, SOME, ANY, ALL. In this article, I will discuss IN BETWEEN and LIKE Operators in SQL Server and in the upcoming articles, I will discuss ALL, ANY, SOME and EXISTS Operators in SQL Server.

Use below script to create database EmployeeDB, Employee table and populate Employee table with some 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

Fetch the records:

Select * From Employee

IN BETWEEN and LIKE Operators

BETWEEN Operator:

The BETWEEN operator in SQL Server is used to get the values within a range. Generally, we use this BETWEEN operator in WHERE clause to get values within a range.

Example:

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 in between 3 and 7.

Following is the output of the above SQL query.

IN BETWEEN and LIKE Operators - BETWEEN

  1. Between Operator returns true if the operand is within a range.
  2. This operator will return the values in between the given range values in the query.
  3. Between operator will return including the starting and ending value also.
  4. This operator support only the AND operator.
  5. When we implement between operator the value always from small to big range values in the query.
NOT BETWEEN Operator

In a SQL statement if we use BETWEEN operator, then it will return record where value between within a range. Suppose if we use NOT keyword with BETWEEN operator then it will return data where column value not in between the range of values.

Example 

SELECT * FROM Employee WHERE ID NOT BETWEEN 3 AND 7

In the above SQL query, we use NOT with BETWEEN operator in SQL. The following query will return all employees details where employee ID not in between 3 and 7 range.

Following is the output of the above SQL query.

IN BETWEEN and LIKE Operators - NOT BETWEEN

IN Operator:

The IN operator in SQL Server is used to search for specified value matches any value in the set of multiple values. Generally, we will use this IN operator in WHERE clause to compare column or variable values with a set of multiple values.

Example:

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

The above SQL query will return all employees whose department is IT or HR.

Following is the output of the above SQL query.

IN BETWEEN and LIKE Operators - IN

NOT IN Operator:

In a SQL query if we use IN operator it will return the data where column value in a set of values. But if we use NOT keyword with IN operator it will return data where column value not in the set of values.

Example:

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

IN BETWEEN and LIKE Operators - NOT IN

LIKE Operator:

The LIKE operator in SQL Server is used to search for character string with the specified pattern using wildcards in the column. In SQL pattern means its specific string of characters with wildcards to search for matched expressions. Generally, we will use this LIKE operator in WHERE clause.

Example 1

The following SQL query will return all employees with Name starts with character ‘P’ followed by any string of characters because we mentioned pattern like ‘P%’. Here ‘%’ is wildcard character which 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.

IN BETWEEN and LIKE Operators - LIKE

Example 2

The following SQL query will return all employees with Name ends with character ‘a’ because we mentioned pattern like ‘%a’. This means return all records with pattern matches like name ends with the character ‘a’.

SELECT * FROM Employee WHERE Name LIKE ‘%a’

Following is the output of the above SQL query.

IN BETWEEN and LIKE Operators - LIKE

Example 3

The following SQL query will return all employees with Name containing the word ‘am’ anywhere in the name column because we mentioned pattern 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

When we use LIKE operator we should use the following symbols
  1. % symbol represent any no of characters in the expression.
  2. _ will represent a single character in the expression.
  3. [] symbol indicates a set of characters in the expression.
  4. [^] will represent any single character, not within the specified range

WAQ to display employee details whose name start with ‘S’ character.

SELECT * FROM Employee WHERE Name LIKE ‘S%’

WAQ to retrieve employee details whose name ends with ‘I’ character.

SELECT * FROM Employee WHERE Name LIKE ‘%I’

WAQ to display employee details whose name 2nd character is ‘O’.

SELECT * FROM Employee WHERE Name LIKE ‘_O%’

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 start with ‘P’ character and ends with ‘A’ character.

SELECT * FROM Employee WHERE Name LIKE ‘P%A’

WAQ to display employee details whose name start 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 will discuss ALL Operator in SQL Server.

SUMMARY

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