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>
 <20251126164356.9a3b7fc25d5118e28175f9a4@wanadoo.fr> <CAKFQuwY2W1WB2qnMuhJ7=GD_rgNmm+N=a7_ZHEtuudw=qajJwg@mail.gmail.com>
 <989b6fd-2979-8ac6-13b-3916d2e47de8@appl-ecosys.com> <c9a9dedb-4a84-49d3-afac-f134ffd62f79@gmail.com>
In-Reply-To: <c9a9dedb-4a84-49d3-afac-f134ffd62f79@gmail.com>
From: Jacqui Caren <jacqui.caren@googlemail.com>
Date: Fri, 28 Nov 2025 10:19:43 +0000
Message-ID: <CABpmC2+wz6HALK8QzzkfZ+Z80bYdbL889=4KKMWd2ew_b4e4FQ@mail.gmail.com>
Subject: Re: Selecting all variations of job title in a list
To: Bryan Sayer <blslists@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009ab9090644a4f89e"
Archived-At: <https://www.postgresql.org/message-id/CABpmC2%2Bwz6HALK8QzzkfZ%2BZ80bYdbL889%3D4KKMWd2ew_b4e4FQ%40mail.gmail.com>
Precedence: bulk

--0000000000009ab9090644a4f89e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

On Wed, Nov 26, 2025, 17:02 Bryan Sayer <blslists@gmail.com> wrote:

> I am not very skilled at Postgresql specifically, but when I was doing SQ=
L
> 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=E2=80=99d still say regexp is not the best solution here - unless y=
ou
> encapsulate the logic in a function.  I suspect you=E2=80=99ll want to us=
e 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=E2=80=99t produce du=
plicate
> matches if you go that route.
>
>
> David,
>
> Thanks,
>
> Rich
>
>
>

--0000000000009ab9090644a4f89e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Many years ago I used a weak precedence engine to categor=
ize this form of job title script to a job code.<div dir=3D"auto"><br></div=
><div dir=3D"auto">The regexp did not work because we had asst to gen mgr</=
div><div dir=3D"auto">The wpe tokenized the words then relaxed token patter=
ns with other token codes with cumulative context</div><div dir=3D"auto">Th=
e final token reduction resulted in a final code or if in my example we had=
 a modifier to role code (asst to xxxx role)</div><div dir=3D"auto"><br></d=
iv><div dir=3D"auto">The entire engine was created in Oracle but would be e=
asy to implement in pgsql. Back then neural nets were only just appearing i=
n finance and llms were non existent.</div><div dir=3D"auto"><br></div><div=
 dir=3D"auto">Old 1960&#39;s tech saved the day</div></div><br><div class=
=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr=
">On Wed, Nov 26, 2025, 17:02 Bryan Sayer &lt;<a href=3D"mailto:blslists@gm=
ail.com">blslists@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <p>I am not very skilled at Postgresql specifically, but when I was
      doing SQL in another environment I would just do</p>
    <p>select distinct (or unique) jobtitle</p>
    <p>usually getting a count of how many times each title occurred.
      Then I would create a mapping to standardize the the job titles.</p>
    <div><b>Bryan Sayer</b><br>
      Retired Demographer/Statistician<br>
      <i>In a world in which you can be anything, <b>be kind</b></i>
      <br>
    </div>
    <div>On 11/26/2025 11:10 AM, Rich Shepard
      wrote:<br>
    </div>
    <blockquote type=3D"cite">On
      Wed, 26 Nov 2025, David G. Johnston wrote:
      <br>
      <br>
      <blockquote type=3D"cite">I was using this tool a while back when I
        was doing heavy regex work.
        <br>
        <br>
        <a href=3D"https://www.regexbuddy.com/" target=3D"_blank" rel=3D"no=
referrer">https://www.regexbuddy.com/</a>
        <br>
        <br>
        Keep in mind the native flavor of regex in PostgreSQL is TCL,
        not Perl.
        <br>
        <br>
        But I=E2=80=99d still say regexp is not the best solution here - un=
less
        you
        <br>
        encapsulate the logic in a function.=C2=A0 I suspect you=E2=80=99ll=
 want to
        use this
        <br>
        logic in more than just a single query and with a literal regexp
        you have
        <br>
        to rely on manual synchronization.=C2=A0 Note, you could combine th=
e
        lookup
        <br>
        table with regexes.=C2=A0 Though beware of ensure you don=E2=80=99t=
 produce
        duplicate
        <br>
        matches if you go that route.
        <br>
      </blockquote>
      <br>
      David,
      <br>
      <br>
      Thanks,
      <br>
      <br>
      Rich
      <br>
      <br>
      <br>
    </blockquote>
  </div>

</blockquote></div>

--0000000000009ab9090644a4f89e--