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 - NOT LIKE Operator in SQL

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

Like Operator in SQL With Example