How does the LIKE operator work in SQL?

The LIKE operator in SQL is used to search for a specified pattern in a column.

The LIKE operator is a logical operator that determines if a character string matches a specified pattern. A pattern can include regular characters and wildcard characters. The operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements.

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

- The percent sign (%): Represents zero, one, or multiple characters. For example, 'a%' would find any values that start with 'a'.
- The underscore (_): Represents a single character. For example, 'a_' would find values that have two characters and start with 'a'.

The LIKE operator is case sensitive. If you want to perform a case-insensitive search, you can use the UPPER or LOWER function to convert all the data to a single case.

Here's an example of how you might use the LIKE operator in a SQL query:

SELECT * FROM Customers
WHERE City LIKE 'london%';

This SQL statement selects all customers whose city starts with 'london'. The % sign is used to configure the pattern so that it matches any customer city that starts with 'london', regardless of what follows after 'london'.

It's important to note that the LIKE operator is not just limited to the beginning or end of a string. You can also use it in the middle of a string. For example, '%don%' would match any city that contains 'don' anywhere in the name.

In summary, the LIKE operator in SQL is a powerful tool for pattern matching in strings. It's used in conjunction with the WHERE clause and can utilise wildcard characters to broaden or narrow down the search.