Re: Selecting all variations of job title in a list

Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> Learning postgres regexp is at the top of my list.

It's well worth knowing a few regex tricks and they're surprisingly
easy to remember. I find these the most useful for ad hoc queries.
'|' for or as mentioned
'()' if you want to check part of an expression eg '(abc|xyz)pqr'
'^' to restrict it to the beginning
'$' to restrict it to the end

Here's an example with your list.
with x(t) as (values ('Asst Gen Mgr.'), ('Env Mgr,'), ('Gen Mgr.'),
('Mgr,'),('Plant Mgr.'))
select * from x where t ~ '(Asst Gen |Gen |Env |Plant |)Mgr(.|,)'

Here's a slightly fancier nested one, just for illustration.
with x(t) as (values ('Asst Gen Mgr.'), ('Env Mgr,'), ('Gen Mgr.'),
('Mgr,'),('Plant Mgr.'))
select * from x where t ~ '^((Asst |)Gen |Env |Plant |)Mgr(.|,)$'

I use regex in my tests and it's practically instant on a few thousand rows.

Thanks, Bernice