The SQL Server LIKE operator is used to search for patterns in a column or string of text data. It is often used in conjunction with the WHERE clause in SQL queries to filter the results based on a particular pattern or substring.

The LIKE operator works by using wildcard characters to represent unknown or variable parts of a pattern. There are two wildcard characters that can be used with the LIKE operator in SQL Server:

1. The percentage sign (%): This represents any sequence of zero or more characters. For example, the pattern 's%' would match any string that starts with the letter 's', such as 'sam', 'susan', or 'sally'.

2. The underscore (_) : This represents any single character. For example, the pattern '_at' would match any string that has a character in the second position followed by the letters 'at', such as 'bat', 'cat', or 'fat'.

Syntax

To use the LIKE operator in a SQL query, the syntax is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Example

For example, to search for all customers in a table named "Customers" whose last name starts with the letter "S", you could use the following SQL statement:

SELECT * FROM Customers
WHERE LastName LIKE 'S%';

This statement would return all rows in the "Customers" table where the LastName column starts with the letter "S".

It is important to note that the LIKE operator is not case-sensitive by default, so 'S%' would match 'smith' and 'Smith' equally. However, you can use the COLLATE keyword to specify a case-sensitive or accent-sensitive comparison if needed.

More examples

Cities table:

Find the city name that contain letters: an.
SELECT * FROM cities
WHERE name LIKE '%an%';

Result:

Find the cities name that start with: Sa.
SELECT * FROM cities
WHERE name LIKE '%Sa%';

Result:

Find the cities name that end with: go.
SELECT * FROM cities WHERE name LIKE '%go';

Result:

In summary, the SQL Server LIKE operator is a powerful tool for searching and filtering data based on patterns or substrings. By using wildcard characters, you can easily find and retrieve the information you need from a database.