Many years ago I used a weak precedence engine to categorize this form of job title script to a job code.
The regexp did not work because we had asst to gen mgr
The wpe tokenized the words then relaxed token patterns with other token codes with cumulative context
The final token reduction resulted in a final code or if in my example we had a modifier to role code (asst to xxxx role)
The entire engine was created in Oracle but would be easy to implement in pgsql. Back then neural nets were only just appearing in finance and llms were non existent.
Old 1960's tech saved the day
I am not very skilled at Postgresql specifically, but when I was doing SQL in another environment I would just do
select distinct (or unique) jobtitle
usually getting a count of how many times each title occurred. Then I would create a mapping to standardize the the job titles.
Bryan Sayer
Retired Demographer/Statistician
In a world in which you can be anything, be kind
On 11/26/2025 11:10 AM, Rich Shepard wrote:
On Wed, 26 Nov 2025, David G. Johnston wrote:
I was using this tool a while back when I was doing heavy regex work.
https://www.regexbuddy.com/
Keep in mind the native flavor of regex in PostgreSQL is TCL, not Perl.
But I’d still say regexp is not the best solution here - unless you
encapsulate the logic in a function. I suspect you’ll want to use this
logic in more than just a single query and with a literal regexp you have
to rely on manual synchronization. Note, you could combine the lookup
table with regexes. Though beware of ensure you don’t produce duplicate
matches if you go that route.
David,
Thanks,
Rich
