Like Operator in SQL Server

The LIKE operator in SQL Server is used in a WHERE clause to search for a specified pattern in a column. It allows you to use wildcards to match one or more characters in a string. The two most commonly used wildcards are:

The percent sign (%) represents zero, one, or multiple characters.
The underscore sign (_) represents one, single character.
The percent sign and the underscore can also be used in combinations!

Here is an example of how to use the LIKE operator in SQL Server:

LIKE Syntax: –

SELECT column1, column2, …FROM table_name
WHERE columnN LIKE pattern;

For instance, the following SQL statement selects all customers whose name starts with the letter ‘a’:

SELECT * FROM Customers

WHERE StudentName LIKE ‘a%’;

You can also combine any number of conditions using AND or OR operators. For example, the following SQL statement selects all customers whose name starts with ‘a’ or ‘b’:

SELECT * FROM Customers

WHERE StudentName LIKE ‘a%’ OR StudentName LIKE ‘b%’;

LIKE Operator	Description
WHERE StudentName LIKE ‘a%’	Finds any values that start with “a”
WHERE StudentName LIKE ‘%a’	Finds any values that end with “a”
WHERE StudentName LIKE ‘%or%’	Finds any values that have “or” in any position
WHERE StudentName LIKE ‘_r%’	Finds any values that have “r” in the second position
WHERE StudentName LIKE ‘a_%’	Finds any values that start with “a” and are at least 2 characters in length
WHERE StudentName LIKE ‘a__%’	Finds any values that start with “a” and are at least 3 characters in length
WHERE StudentName LIKE ‘a%o’	Finds any values that start with “a” and ends with “o”

The following SQL statement selects all customers with a StudentName starting with “a”:

SELECT * FROM Customers WHERE StudentName LIKE ‘a%’;

The following SQL statement selects all customers with a StudentName that have “or” in any position:

SELECT * FROM Customers WHERE StudentName LIKE ‘%or%’;

The following SQL statement selects all customers with a StudentName that have “r” in the second position:

SELECT * FROM Customers WHERE StudentName LIKE ‘_r%’;

The following SQL statement selects all customers with a StudentName that starts with “a” and are at least 3 characters in length:

SELECT * FROM Customers WHERE StudentName LIKE ‘a__%’;

The following SQL statement selects all customers with a StudentName that starts with “a” and ends with “o”:

SELECT * FROM Customers WHERE StudentName LIKE ‘a%o’;

The following SQL statement selects all customers with a StudentName that does NOT start with “a”:

SELECT * FROM Customers WHERE StudentName NOT LIKE ‘a%’;