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 OperatorDescription
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%\’;