The LIKE operator is used in SQL to perform pattern matching within string data. It allows you to search for a specified pattern in a column. The LIKE operator can be combined with two wildcard characters:
1. Percent Sign (%): Represents zero or more characters.
2. Underscore (_): Represents a single character.
Basic Syntax
SELECT *
FROM table_name
WHERE column_name LIKE pattern;
Examples
1. Using % Wildcard:
- To find all records where a column's value starts with "A":
SELECT *
FROM employees
WHERE first_name LIKE 'A%';
- To find all records where a column's value ends with "son":
SELECT *
FROM employees
WHERE last_name LIKE '%son';
- To find all records where a column's value contains "ing":
SELECT *
FROM products
WHERE product_name LIKE '%ing%';
2. Using _ Wildcard:
- To find all records where a column's value has "a" as the second character:
SELECT *
FROM users
WHERE username LIKE '_a%';
- To find all records where a column's value has a specific pattern, e.g., a three-letter sequence that starts with "c" and ends with "t":
SELECT *
FROM codes
WHERE code LIKE 'c_t';
3. Combining Wildcards:
- You can combine both wildcards in a single pattern:
SELECT *
FROM products
WHERE name LIKE 'A_%_C%';
Case Sensitivity
- The case sensitivity of the LIKE operator can depend on the database you are using. For example, some databases like PostgreSQL are case-sensitive, while MySQL is case-insensitive by default.
Summary
The LIKE operator is a powerful tool for searching within string columns and provides great flexibility through the use of wildcards. By correctly applying patterns, you can retrieve specific data based on partial matches.