SQL Basics
SQL LIKE
Pattern Matching with LIKE
LIKE enables pattern matching with wildcards, and ILIKE in PostgreSQL offers case-insensitive matching.
Introduction to SQL LIKE
The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It allows for more flexible string searches by using wildcard characters. The most common wildcards are the percentage sign (%) and the underscore (_).
- %: Represents zero or more characters.
- _: Represents a single character.
Basic SQL LIKE Syntax
Using Wildcards with LIKE
Wildcards help in creating complex search patterns. Below are examples of how to use these wildcards:
'a%'finds any values that start with "a".'%a'finds any values that end with "a".'%or%'finds any values that have "or" in any position.'_r%'finds any values that have "r" in the second position.'a_%_%'finds any values that start with "a" and are at least 3 characters long.
Case-Insensitive Matching with ILIKE in PostgreSQL
PostgreSQL offers the ILIKE operator for case-insensitive pattern matching, making it easier to match patterns without worrying about the case sensitivity of the strings.
Practical Examples of LIKE and ILIKE
Consider a database of books where you want to search for titles that contain the word "SQL" in any case. Using LIKE and ILIKE, you can perform the following queries:
Conclusion
The LIKE operator is a powerful tool for searching patterns in text columns, using wildcards to match flexible criteria. For case-insensitive searches, PostgreSQL's ILIKE offers a convenient alternative. Understanding these operators enhances your ability to query databases effectively.
