sql_like

SQL LIKE Operator: Pattern Matching in Strings

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column containing text data. It’s a powerful tool for flexible string comparisons.

The SQL LIKE Operator Explained

When you need to find rows where a column’s string value matches a certain pattern rather than an exact string, the LIKE operator is your solution. It uses wildcard characters to define these patterns.

SQL LIKE Syntax

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

Standard Wildcard Characters:

• % (Percent sign): Represents zero, one, or multiple characters.
  • 'A%': Matches any string starting with ‘A’. (e.g., ‘Apple’, ‘Ant’, ‘A’)
  • '%Z': Matches any string ending with ‘Z’. (e.g., ‘Buzz’, ‘Quiz’)
  • '%word%': Matches any string containing ‘word’. (e.g., ‘keyword’, ‘password’)
• _ (Underscore): Represents a single character.
  • 'H_t': Matches ‘Hot’, ‘Hat’, ‘Hit’.
  • '__X%': Matches any string where ‘X’ is the third character.

Case Sensitivity: The LIKE operator’s case sensitivity depends on the database system and the column’s collation settings. Some databases are case-sensitive by default (e.g., PostgreSQL), while others are not (e.g., SQL Server, MySQL on Windows). You can often use functions like LOWER() or UPPER() on both the column and the pattern for case-insensitive searches if needed: WHERE LOWER(column_name) LIKE LOWER(pattern);

Demonstration Database: Northwind

Let’s use the Northwind sample database.

A selection from the “Customers” table:

CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country


1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany


2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico


3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico


4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
UK


5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden

SQL LIKE Operator Examples

1. Select all customers with a City starting with the letter “L”:

SELECT CustomerName, City
FROM Customers
WHERE City LIKE 'L%';

2. Select all customers with a City ending with the letter “n”:

SELECT CustomerName, City
FROM Customers
WHERE City LIKE '%n';

3. Select all customers with a Country containing the pattern “land”:

SELECT CustomerName, Country
FROM Customers
WHERE Country LIKE '%land%';

4. Select all customers whose CustomerName has ‘o’ as the second letter:

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '_o%';

Using NOT LIKE

The NOT LIKE operator allows you to select records that DO NOT match the pattern.

5. Select all customers with a Country NOT containing the pattern “land”:

SELECT CustomerName, Country
FROM Customers
WHERE Country NOT LIKE '%land%';

Escaping Wildcard Characters:

If you need to search for a literal % or _ character, you must use the ESCAPE clause. For example, to find names containing ‘50%’:

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%50\%%' ESCAPE '\'; -- '\' is the escape character here

Practical Application: LIKE is invaluable for search functionalities, data filtering based on partial information, and data validation rules.