[PostgreSQL] How to select rows from a table matching a given pattern

Question

I want to find all rows in a table where a column matches the pattern T39.{1-2}X2* i.e. column should have T39.1X2* or T39.2X2*. Is there any operator other than LIKE that I can use for pattern matching?

Answer 1

You can use the "SIMILAR TO" operator to match such patterns.

Here is an SQL example to match the pattern T39.{1-2}X2*:

select * from TABLE_NAME where COLUMN_NAME SIMILAR TO 'T39.[1-2]X2%'

The above query will select all rows with the column containing T39.1X2* or T39.2X2*.