Subject: Re: Selecting all variations of job title in a list

From: Vincent Veyron <[email-masked]>
Date: Wed, 26 Nov 2025 15:48:44 +0100

On Tue, 25 Nov 2025 08:33:07 -0800 (PST)
Rich Shepard <[email-masked]> wrote:

> Companies can have slightly different titles for the same job; for example
> (using abbreviations). 'Asst Gen Mgr.', 'Env Mgr,', 'Gen Mgr,'. 'Mgr,',
> 'Plant Mgr.'
> 
> I want to select all people table rows that contain these varieties. I know
> the 'like' operator uses '%' as a wildcard, but is not accepted in an 'in'
> list.
> 
> Is there a way to use a multicharacter wildcard in an 'in' list?
> 

Hi Rich,

As Ron wrote, a regexp would do it, I guess?

SELECT * FROM people WHERE title ~ 'Asst Gen Mgr|Env Mgr|Gen Mgr|Mgr|Plant Mgr';

Although in your example, you would get the same result with just:

SELECT * FROM people WHERE title ~ 'Mgr';

https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP

-- 

					Bien à vous, Vincent Veyron 

https://marica.fr/ 
Logiciel de suivi des contentieux juridiques, des sinistres d'assurance et des contrats

---

From: Rich Shepard <[email-masked]>
Date: Wed, 26 Nov 2025 07:10:16 -0800 (PST)

> https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Vincent,

Learning postgres regexp is at the top of my list.

Thanks,

Rich

---

From: Bernice Southey <[email-masked]>
Date: Fri, 28 Nov 2025 12:20:37 +0000

> 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