CODEWITHSUNDEEP
regexpostgresql
David
David

Reputation: 5016

Using regex in WHERE in Postgres

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

Upvotes: 116

Views: 114621

Answers (1)

araqnid
araqnid

Reputation: 133482

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.

Upvotes: 205

Related Questions