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?

Sebastian Paaske Tørholm · Accepted Answer · 2013-09-19 12:13:05Z

6

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+$');

1 Comment

E signifies that it is an escape string constant (section 4.1.2.1), i.e. a string constant that allows you to do string escapes such as \\ .

Mani Deep · Accepted Answer · 2013-09-19 12:57:24Z

2

you simply can use LIKE and %

ie.,

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

answered Sep 19, 2013 at 12:57

Mani Deep

1,3562 gold badges18 silver badges33 bronze badges

The number bit is important