Content-Type: multipart/alternative;
 boundary="------------v4e4pDcqoHHB0dwfd55mSbrG"
Message-ID: <c9a9dedb-4a84-49d3-afac-f134ffd62f79@gmail.com>
Date: Wed, 26 Nov 2025 12:01:46 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Selecting all variations of job title in a list
To: pgsql-general@lists.postgresql.org
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>
From: Bryan Sayer <blslists@gmail.com>
Content-Language: en-US
In-Reply-To: <989b6fd-2979-8ac6-13b-3916d2e47de8@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/c9a9dedb-4a84-49d3-afac-f134ffd62f79%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------v4e4pDcqoHHB0dwfd55mSbrG
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
>
>
--------------v4e4pDcqoHHB0dwfd55mSbrG
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <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 class="moz-signature"><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 class="moz-cite-prefix">On 11/26/2025 11:10 AM, Rich Shepard
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:989b6fd-2979-8ac6-13b-3916d2e47de8@appl-ecosys.com">On
      Wed, 26 Nov 2025, David G. Johnston wrote:
      <br>
      <br>
      <blockquote type="cite">I was using this tool a while back when I
        was doing heavy regex work.
        <br>
        <br>
        <a class="moz-txt-link-freetext" href="https://www.regexbuddy.com/">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’d still say regexp is not the best solution here - unless
        you
        <br>
        encapsulate the logic in a function.  I suspect you’ll 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.  Note, you could combine the
        lookup
        <br>
        table with regexes.  Though beware of ensure you don’t produce
        duplicate
        <br>
        matches if you go that route.
        <br>
      </blockquote>
      <br>
      David,
      <br>
      <br>
      Thanks,
      <br>
      <br>
      Rich
      <br>
      <br>
      <br>
    </blockquote>
  </body>
</html>

--------------v4e4pDcqoHHB0dwfd55mSbrG--