REGEXP Function in PostgreSQL

In PostgreSQL, you can use the ~ operator or the REGEXP_MATCHES function to perform regular expression matching. Here's an example using the ~ operator:

Suppose you have a table named products with a column product_name, and you want to find all products whose names start with "Apple":

SELECT product_name

FROM products

WHERE product_name ~ '^Apple';

In this example:

- product_name is the column containing product names.

- The ~ operator is used to match product names using a regular expression.

- '^Apple' is the regular expression pattern. The ^ asserts the start of the string, so it matches product names that start with "Apple".

This query will return all product names from the products table where the name starts with "Apple".

You can adjust the regular expression pattern to match different patterns according to your requirements. For example, to match any product name containing "Apple" anywhere in the string, you could use product_name ~ 'Apple'.

Alternatively, you can use the REGEXP_MATCHES function to extract substrings from a string using a regular expression pattern. Here's an example:

SELECT REGEXP_MATCHES(product_name, '^Apple.*') AS product_name_matches

FROM products;

This will return an array of matching substrings for each product_name that starts with "Apple".