YouTip

# PostgreSQL LIKE Operator In PostgreSQL, when you need to query data that matches a specific pattern rather than an exact value, you can use the **LIKE** operator. The `LIKE` operator is typically combined with wildcards to represent arbitrary characters. If no wildcards are used, the `LIKE` operator behaves exactly like the equal sign (`=`) operator. --- ## Wildcards in PostgreSQL PostgreSQL supports two main wildcards with the `LIKE` operator: * **Percent sign (`%`)**: Represents zero, one, or multiple characters. * **Underscore (`_`)**: Represents a single, specific character. --- ## Syntax The basic syntax for using the `LIKE` operator with the `%` and `_` wildcards in a `SELECT` statement is as follows: ```sql SELECT * FROM table_name WHERE column LIKE 'pattern%'; -- Or SELECT * FROM table_name WHERE column LIKE '%pattern%'; -- Or SELECT * FROM table_name WHERE column LIKE 'pattern_'; -- Or SELECT * FROM table_name WHERE column LIKE '_pattern'; -- Or SELECT * FROM table_name WHERE column LIKE '_pattern_'; ``` ### Key Rules: * You can specify any condition inside the `WHERE` clause. * Multiple conditions can be combined using the `AND` or `OR` operators. * The pattern can contain any combination of numbers, letters, and wildcards. --- ## Pattern Matching Examples The following table demonstrates how different combinations of `%` and `_` wildcards behave: | Example | Description | | :--- | :--- | | `WHERE SALARY::text LIKE '200%'` | Finds any values in the `SALARY` column that start with **200**. | | `WHERE SALARY::text LIKE '%200%'` | Finds any values in the `SALARY` column that contain **200** in any position. | | `WHERE SALARY::text LIKE '_00%'` | Finds any values in the `SALARY` column that have **00** in the second and third positions. | | `WHERE SALARY::text LIKE '2_%_%'` | Finds any values in the `SALARY` column that start with **2** and are at least 3 characters in length. | | `WHERE SALARY::text LIKE '%2'` | Finds any values in the `SALARY` column that end with **2**. | | `WHERE SALARY::text LIKE '_2%3'` | Finds any values in the `SALARY` column that have **2** in the second position and end with **3**. | | `WHERE SALARY::text LIKE '2___3'` | Finds any values in the `SALARY` column that start with **2**, end with **3**, and are exactly 5 characters long. | > **Note:** In PostgreSQL, the `LIKE` operator is strictly used for string comparisons. If you want to perform pattern matching on numeric types (such as `INTEGER` or `NUMERIC`), you must cast the column to a string type using the cast operator `::text` (e.g., `SALARY::text`). --- ## Practical Examples To demonstrate these concepts, let's set up a sample table named `COMPANY`. ### 1. Preparing the Sample Data First, create the `COMPANY` table and insert some records: ```sql -- Create the COMPANY table CREATE TABLE COMPANY ( id INT PRIMARY KEY NOT NULL, name TEXT NOT NULL, age INT NOT NULL, address CHAR(50), salary REAL ); -- Insert sample records INSERT INTO COMPANY (id, name, age, address, salary) VALUES (1, 'Paul', 32, 'California', 20000), (2, 'Allen', 25, 'Texas', 15000), (3, 'Teddy', 23, 'Norway', 20000), (4, 'Mark', 25, 'Rich-Mond', 65000), (5, 'David', 27, 'Texas', 85000), (6, 'Kim', 22, 'South-Hall', 45000), (7, 'James', 24, 'Houston', 10000); ``` Verify the table contents: ```sql youtipdb=# SELECT * FROM COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows) ``` --- ### 2. Matching Numeric Fields (Using Type Casting) The following query finds all records where the `AGE` starts with the digit **2**. Since `AGE` is an integer, we cast it to `text` using `::text`: ```sql youtipdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%'; ``` **Output:** ```text id | name | age | address | salary ----+-------+-----+-------------+-------- 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (6 rows) ``` --- ### 3. Matching String Fields with Wildcards The following query finds all records where the `ADDRESS` contains a hyphen (`-`): ```sql youtipdb=# SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%'; ``` **Output:** ```text id | name | age | address | salary ----+------+-----+-------------------------------------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 (2 rows) ``` --- ## Important Considerations ### Case Sensitivity The `LIKE` operator in PostgreSQL is **case-sensitive**. For example, `LIKE 'paul%'` will not match `'Paul'`. * If you need a case-insensitive match, use the **`ILIKE`** operator instead: ```sql SELECT * FROM COMPANY WHERE name ILIKE 'paul%'; ``` ### Performance and Indexing * Pattern matching queries that start with a wildcard (e.g., `LIKE '%pattern'`) cannot utilize standard B-Tree indexes, which can lead to slow full-table scans on large datasets. * If you frequently perform prefix searches (e.g., `LIKE 'pattern%'`), ensure your column is indexed. For advanced pattern matching performance, consider using PostgreSQL's `pg_trgm` extension with GIN or GiST indexes.