Using regex in WHERE in Postgres

Question

I currently have the the following query:

select regexp_matches(name, 'foo') from table;

How can I rewrite this so that the regex is in the where like the following (not working):

select * from table where regexp_matches(name, 'foo');

Current error message is: ERROR: argument of WHERE must be type boolean, not type text[] SQL state: 42804 Character: 29

Chuck Wilbur · Accepted Answer · 2015-02-03 20:42:37Z

up vote 59 down vote accepted

Write instead:

select * from table where name ~ 'foo'

The '~' operator produces a boolean result for whether the regex matches or not rather than extracting the matching subgroups.

Chuck Wilbur

1,33011525

answered Feb 9 '11 at 13:32

araqnid

57.7k149999

add a comment |

Maxim Sloyko · Answer 2 · 2011-02-09 13:32:59Z

up vote 23 down vote

Just use match operator there:

select * from table where name ~ 'foo';

answered Feb 9 '11 at 13:32

Maxim Sloyko

6,70622341

add a comment |

2 Answers 2