Like and NOT LIKE Operator in SQL With Examples

.

Like clause in SQL- SQL Like and NOT LIKE Operator 

Like Operator

In SQL like operator is used in a where clause to compare a value to similar values using the wildcards operator.

two wildcards were used in conjunction with the LIKE operator. SQL LIKE is a logical operator.

• %: Percent
• _ : Underscore

(%) Percent

The Percent wildcard sign represents any string of zero, one, or more characters.

(-) Underscore 

The underscore wildcard sign represents any single character.

Note: We can use the percent sign and the underscore in combinations!

Syntax of Like Operator:

The basic syntax of the like operator is given below.

SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;

Example of the like operator with (%) and (_) wildcard

1) a%: Finds any values that start with "a"

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE 'a%';

The above SQL Statement will display all the employee names whose names start with "a".

 

2) %a: Finds any values that end with "a"

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE '%a';

The above SQL Statement will display all the employee names whose names end with "a".

3) o%a: Find any values that start with "o" and end with "a".

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE 'o%a';

The above SQL Statement will display all the employee names whose names start with "o" and end with "a".

4) %ab%: Finds any Value that has the "%ab%".

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE '%ab%';

The above SQL Statement will display all the employee names whose names contain "ab".

5) _r%: Find the value that has "r" in the second position in the column.

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE '_r%';

The above SQL Statement will display all the employee names whose names have "r" in the Second Position.

6) _ab%: Finds any Value that has the ("a" in the second position and "b" in the third position.)

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE '_ab%';

The above SQL Statement will display all the employee names whose names contain "a" in the second position and "b" in the third position.

7) _a%b: Finds any Value that has the ("a" in the second position and ends with b.)

Example:

SELECT * FROM Employee WHERE EmployeeName LIKE '_a

%b';

The above SQL Statement will display all the employee names whose names contain ("a" in the second position and ends with b.)

8) a_%:Finds any values that start with "a" and are at least 2 characters in length.

Example

SELECT * FROM Employee WHERE EmployeeName LIKE 'a__%';

The above SQL Statement will display selects all employees with  Employee that start with "a" and are at least 2 characters in length

SQL NOT LIKE

In SQL the NOT like operator is used in a where clause to compare a value to not similar values using the wildcards operator.

Syntax of NOT LIKE Operator:

The basic syntax of the NOT like operator is given below.

SELECT column1, column2, ... FROM table_name WHERE column NOT LIKE pattern;

Example of NOT LIKE Operator

Example of a NOT LIKE Operator is Given Below 

-- select customers who don't live in the USA

SELECT * FROM Customers

WHERE country NOT LIKE 'USA';

Explanation:
The SQL command selects all customers except those whose country is the USA.

Hope!!! The above Tutorial on Like Operator with examples is helpful For You...

Team, 

QA acharya

Tags: Like operator in SQL, SQL like Operator, Example of like operators, Like operator with an example, Like clause with example

Like Operator in SQL With Example