4

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?

Community
  • 1
  • 1
dagda1
  • 26,856
  • 59
  • 237
  • 450

2 Answers2

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

See also:

Sebastian Paaske Tørholm
  • 49,493
  • 11
  • 100
  • 118
  • `E` signifies that it is an [escape string constant](http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html) (section 4.1.2.1), i.e. a string constant that allows you to do string escapes such as `\\ `. – Sebastian Paaske Tørholm Sep 19 '13 at 13:47
2

you simply can use LIKE and %

ie.,

SELECT name FROM deals WHERE name LIKE 'Untitled Deal %'
Mani Deep
  • 1,298
  • 2
  • 17
  • 33