Italian Trulli

Like and NOT LIKE Operator in SQL With Examples

Test Tribe
.
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) %aFinds 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


Post a Comment

0 Comments