PostgreSQL: how to write WHERE clause in SQL using regular expression

Question

I want to select all rows where the column value starts with "X7", followed by at least one digit between 1 and 9, then followed by zero or more digits (0-9), i.e., something like X7[1-9][0-9]*. Can I use regular expression like syntax in the WHERE clause for PostgreSQL?

Answer 1

Yes, PostgreSQL allows regular expressions in the WHERE clause. Here is an example using your given regular expression.

The following SQL is based on PostgreSQL:

SELECT *
FROM table_name
WHERE column_name ~ '^X7[1-9][0-9]*'; 

In the above query, you can replace table_name with your table name and column_name with the column you want to choose.