public inbox for [email protected]  

help / color / mirror / Atom feed

Re: Selecting all variations of job title in a list
3+ messages / 3 participants
[nested] [flat]

* Re: Selecting all variations of job title in a list
@ 2025-11-26 14:48  Vincent Veyron <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Vincent Veyron @ 2025-11-26 14:48 UTC (permalink / raw)
  To: [email protected]

On Tue, 25 Nov 2025 08:33:07 -0800 (PST)
Rich Shepard <[email protected]> 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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Selecting all variations of job title in a list
@ 2025-11-26 15:10  Rich Shepard <[email protected]>
  parent: Vincent Veyron <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Rich Shepard @ 2025-11-26 15:10 UTC (permalink / raw)
  To: [email protected]

On Wed, 26 Nov 2025, Vincent Veyron wrote:

> 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

Vincent,

Learning postgres regexp is at the top of my list.

Thanks,

Rich






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Selecting all variations of job title in a list
@ 2025-11-28 12:20  Bernice Southey <[email protected]>
  parent: Rich Shepard <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: Bernice Southey @ 2025-11-28 12:20 UTC (permalink / raw)
  To: Rich Shepard <[email protected]>; +Cc: [email protected]

Rich Shepard <[email protected]> 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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2025-11-28 12:20 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-26 14:48 Re: Selecting all variations of job title in a list Vincent Veyron <[email protected]>
2025-11-26 15:10 ` Rich Shepard <[email protected]>
2025-11-28 12:20   ` Bernice Southey <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox