SQL LIKE Operator
The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is often used with the SELECT statement to retrieve rows matching a pattern.
Basic Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;Using Wildcards with LIKE
The LIKE operator is often used with the following two wildcards:
%– Represents zero, one, or multiple characters_– Represents a single character
Examples
Example 1: Searching for a Pattern
SELECT * FROM customers
WHERE customer_name LIKE 'A%';This query selects all customers whose names start with the letter ‘A’.
Example 2: Searching for a Pattern Anywhere in the Text
SELECT * FROM customers
WHERE customer_name LIKE '%son%';This query selects all customers whose names contain the substring ‘son’.
Example 3: Searching for a Pattern at the End
SELECT * FROM customers
WHERE customer_name LIKE '%n';This query selects all customers whose names end with the letter ‘n’.
Example 4: Using _ Wildcard
SELECT * FROM customers
WHERE customer_name LIKE 'Jo_n';This query selects all customers whose names are four letters long and start with ‘Jo’ and end with ‘n’.
Example 5: Combining % and _ Wildcards
SELECT * FROM products
WHERE product_code LIKE '12_%_202%';This query selects all products whose product codes start with ’12’, followed by any character, followed by any single character, followed by ‘202’, and ending with any character or characters.
Using LIKE with NOT
The LIKE operator can be combined with NOT to find rows that do not match a pattern.
SELECT * FROM customers
WHERE customer_name NOT LIKE 'A%';This query selects all customers whose names do not start with the letter ‘A’.
Case Sensitivity
The case sensitivity of the LIKE operator depends on the database system. For example, in SQL Server, the LIKE operator is case-insensitive by default, while in MySQL, it is case-sensitive unless the column uses a case-insensitive collation.
The LIKE operator is a powerful tool for pattern matching in SQL queries, allowing for flexible and precise searches within text data.