Like Operator in SQL With Example

.
Like clause in SQL

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 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%aFinds 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 starts with "a" and are at least 2 characters in length:

Like Operator in SQL With Example


.

Post a Comment

0 Comments