Like Operator in SQL Server with Example

  Total Views : 439
  Comments : 0
 
  Keywords : SQL like operator example, SQL like operator with query, SQL like operator syntax with example, SQL like operator in where clause example, SQL like operator with not keyword example

Here we will learn what is like operator in sql server, how to use like operator in sql server, use of like operator in sql server and how to use like operator with not keyword in sql server.

SQL LIKE Operator

The LIKE operator in SQL is used to search for character string with specified pattern using wildcards in column. In SQL pattern means its specific string of characters with wild cards to search for matched expressions. Generally we will use this LIKE operator in WHERE clause and syntax for LIKE operator in SQL will be like as shown below 

Syntax of SQL LIKE Operator

Following sql statement represent syntax of sql like operator

 

SELECT column1, column2 FROM tablename WHERE column1 LIKE 'SomePatternwithwildcard'

Note: You will Learn more about sql wildcards in next chapter.

 

We will check sql like operator with example for that first create “EmployeeDetails” table by using following script

 

create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))

 

insert into EmployeeDetails

values(1,'suresh','software engineer',25000,'chennai'),

(2,'rohini','AEO',15000,'chennai'),

(3,'madhavsai','business analyst',50000,'nagpur'),

(4,'mahendra','CA',75000,'guntur'),

(5,'sateesh','Doctor',65000,'guntur')

 

select * from EmployeeDetails

Once we run above SQL script our table “EmployeeDetails” will create and result will be like as shown below

 

Newly created employeedetails table in sql server

 Now run following examples to check LIKE operator in SQL

SQL LIKE Operator Example 1

The following SQL query will return all employees with location starts with character 'c' followed by any string of characters because we mentioned pattern like 'c%'. Here '%' is wildcard character which we will use before or after characters to search for required matched string of characters.

 

SELECT * FROM EmployeeDetails WHERE Location LIKE 'c%'

Once we run above sql query our sql like operatpr example result will be like as shown below

 

Output

 

Following is the output of sql like operator example

 

SQL Like Operator Example Result or Output

SQL Like Operator Example 2

The following SQL query will return all employees with location end with character 'r' because we mentioned pattern like '%r'. This means return all records with pattern matches like location ends with character 'r'.

 

SELECT * FROM EmployeeDetails WHERE Location LIKE '%r'

When we run above sql query we will get sql like operator result will be like as shown below

 

Output

 

Following is the output of sql like operator example

 

SQL Like

SQL Like Operator Example 3

The following SQL query will return all employees with location containing word 'en' anywhere in the location column because we mentioned pattern like '%en%'. This means it will check for the respective word anywhere in column irrespective of characters in front or back.

 

SELECT * FROM EmployeeDetails WHERE Location LIKE '%en%'

When we run above sql query we will get sql like operator result like as shown below

 

Output

 

Following is the output of sql like opeator example

 

SQL Like Operator Example Result or Output

Now we will see how to use NOT keyword with LIKE operator and will check how it will return records. Generally if we use NOT keyword with LIKE operator it will return all the records that does not match the pattern.

SQL LIKE Operator with NOT Keyword Example

The following SQL statement will return all employees with location not containing word 'en' anywhere in the location column because we used NOT keyword with LIKE operator and mentioned pattern like '%en%'.

 

SELECT * FROM EmployeeDetails WHERE Location NOT LIKE '%en%'

When we run above sql query we will get result of sql like operator with not keyword example will be like as shown below

 

Output

 

Following is the output of sql like operator with not keyword example

 

SQL Server Like Operator with Not Keyword Example

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User