MIME-Version: 1.0
References: <c5296761-2f10-a59b-ad16-a0189e57cd5a@appl-ecosys.com>
 <20251126154844.e6e3d5c7f476cc966fc201f4@wanadoo.fr> <29ffee7e-40a8-f911-4419-24ba8558f5db@appl-ecosys.com>
In-Reply-To: <29ffee7e-40a8-f911-4419-24ba8558f5db@appl-ecosys.com>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Fri, 28 Nov 2025 12:20:37 +0000
Message-ID: <CAEDh4nwz7aZraJzy0NZC5+e4LH9MqfL7V0o+XF332dus-HFUSA@mail.gmail.com>
Subject: Re: Selecting all variations of job title in a list
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nwz7aZraJzy0NZC5%2Be4LH9MqfL7V0o%2BXF332dus-HFUSA%40mail.gmail.com>
Precedence: bulk

Rich Shepard <rshepard@appl-ecosys.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