The LIKE operator is used in a WHERE clause to search for a specific pattern in a column.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern';
  • Case-insensitive by default in SQL Server (depends on collation).
  • Supports wildcards to define patterns.

Step 1: SQL Server Wildcards

Wildcard Meaning Example
% Any sequence of characters (0 or more) 'A%' → starts with A
_ Any single character 'A_' → two characters, first is A
[] Any single character within the brackets '[AB]%' → starts with A or B
[^] Any single character not in brackets '[^A]%' → does not start with A

Step 2: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department
1 John Doe IT
2 Jane Smith HR
3 Mike Brown IT
4 Sara White HR
5 Tom Green IT

Step 3: Using % wildcard

Example: Names starting with ‘J’

SELECT FirstName
FROM Employees
WHERE FirstName LIKE 'J%';

Result:

FirstName
John
Jane

Example: Names ending with ‘e’

SELECT FirstName
FROM Employees
WHERE FirstName LIKE '%e';

Result:

FirstName
Jane
Mike

Example: Names containing ‘ar’

SELECT FirstName
FROM Employees
WHERE FirstName LIKE '%ar%';

Result:

FirstName
Sara

Step 4: Using _ wildcard

Example: Two-letter names starting with ‘T’

SELECT FirstName
FROM Employees
WHERE FirstName LIKE 'T_';

Result:

FirstName
Tom

_ matches exactly one character.

Step 5: Using character sets []

Example: Names starting with J or M

SELECT FirstName
FROM Employees
WHERE FirstName LIKE '[JM]%';

Result:

FirstName
John
Jane
Mike

Step 6: Using negation [^]

Example: Names not starting with J

SELECT FirstName
FROM Employees
WHERE FirstName LIKE '[^J]%';

Result:

FirstName
Mike
Sara
Tom

Step 7: Using LIKE with NOT

Example: Names not ending with ‘e’

SELECT FirstName
FROM Employees
WHERE FirstName NOT LIKE '%e';

Result:

FirstName
John
Sara
Tom

Categorized in:

SQL Server,