Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL LIKE

The `LIKE` string function in PostgreSQL is used to match text patterns within column values. It allows you to search for specific sequences of characters in string data types.

Usage

The `LIKE` function is commonly used in `SELECT` statements to filter data based on specific text patterns. It is especially useful for finding rows where a column's value matches a given pattern.

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

In this syntax, `column_name LIKE pattern` checks if `column_name` matches the specified `pattern`, which can include wildcard characters like `%` and `_`.

Examples

1. Basic Usage with Wildcard %

SELECT *
FROM products
WHERE product_name LIKE 'Apple%';

This example retrieves all rows from the `products` table where `product_name` starts with "Apple".

2. Pattern Matching with Wildcard _

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'J_n%';

This query selects customers whose `customer_name` starts with "J", has any single character in the second position, and is followed by "n".

3. Combining LIKE with NOT Operator

SELECT order_id, order_date
FROM orders
WHERE order_status NOT LIKE 'Cancelled%';

This example fetches orders from the `orders` table where `order_status` does not start with "Cancelled".

4. Using ESCAPE Clause

SELECT comment_id
FROM comments
WHERE comment_text LIKE '100\% sure' ESCAPE '\';

This query finds comments that contain the text "100%" literally, using the backslash `\` to escape the `%` character.

Tips and Best Practices

  • Use `%` and `_` wisely. The `%` wildcard matches any sequence of characters, while `_` matches a single character.
  • Optimize for performance. Be cautious with `%` at the start of patterns as it can slow down query performance. Note that indexes are generally not used when the pattern starts with a wildcard.
  • Consider case sensitivity. Use `ILIKE` for case-insensitive pattern matching if needed.
  • Combine with other conditions. Use `LIKE` with additional `WHERE` conditions to narrow down results effectively.
  • Escape special characters. Use the `ESCAPE` clause to search for literal `%` or `_` characters when needed, as shown in the example.
  • Explore alternatives. Consider using `SIMILAR TO` for more complex pattern matching needs, as it supports regular expressions.