SQL LIKE Clause

The SQL LIKE clause is used to compare a value to similar values using wildcard operators.

There are two wildcards used in conjunction with the LIKE operator.

  • The percent sign (%)
  • The underscore (_)

Syntax:

SELECT column1, column2 FROM table_name WHERE column LIKE pattern;

 

Example

The following table has a few examples showing the WHERE part having different LIKE clause with '%' and '_' operators.

Sr.No. Syntax & Description
1

WHERE name LIKE 'Amit%'

Finds any character that start with Amit.
2

WHERE name LIKE '_am%'

Finds any character that have am in the second and third positions.
3

WHERE name LIKE '%Amit%'

Finds any character that have Amit in any position.
4

WHERE name LIKE '%A'

Finds any character that end with A.
5

WHERE name LIKE 'A_%_%_%'

Finds any character that start with A and are at least 4 characters in length.
6

WHERE name LIKE 'A__t'

Finds any character in a four-digit character that start with A and end with t.
7

WHERE name LIKE '_A%m'

Finds any character that have a A in the second position and end with a m.

 

Example Database:

This is  customers  table.

id name Location
1 Kailash Delhi
2 Amit Haryana
3 Siddarth Noida
4 Vikas Noida

 

 

Example: The following SQL statement selects all customers with a name starting with "a":

Syntax:

SELECT * FROM customers WHERE name LIKE 'A%';

Result:

id name Location
2 Amit Haryana