Regular Expression for Postgres

Question

I want to pull a particular field from a postgres table that conforms to this pattern:

/^Untitled Deal \d+$/

For example:

Untitled Deal 1

Untitled Deal 2

Untitled Deal 3

I have the query in postgres which is not working:

SELECT "name" FROM "deals" WHERE ("name" ILIKE '/^Untitled Deal \\d+$/');

Can anyone point out what I am doing wrong?

Answer 1

You need to use ~* instead of ILIKE, if you want to pattern match against POSIX-style regular expressions.

I.e.:

SELECT "name" FROM "deals" WHERE ("name" ~* E'^Untitled Deal \\d+$');

See also:

• PostgreSQL documentation on pattern matching

Answer 2

you simply can use LIKE and %

ie.,

SELECT name FROM deals WHERE name LIKE 'Untitled Deal %'