MIME-Version: 1.0
References: 
 <CADHFRch1=ND1TpiOidJELBdJJSugn-H1d3vyjg9gh9Nw1dZCgg@mail.gmail.com>
 <CAKAnmmKhepxPzLWiU2JMNWsNiwLmQAyKCgV-f2dMot1hKVa-hA@mail.gmail.com>
 <CAJexoSK3grH+4khLYMDH=dBwbZxx=F-5GUM1rzHh5fzT_GGuwg@mail.gmail.com>
In-Reply-To: 
 <CAJexoSK3grH+4khLYMDH=dBwbZxx=F-5GUM1rzHh5fzT_GGuwg@mail.gmail.com>
From: Bo Guo <bo.guo@gisticinc.com>
Date: Mon, 4 Dec 2023 18:39:18 -0700
Message-ID: 
 <CADHFRcj_4uaxjNSJCCmB8LBE3w5BHh8a7u3pMpZiyDQSbawTyA@mail.gmail.com>
Subject: Re: Overcoming Initcap Function limitations?
To: Steve Midgley <science@misuse.org>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004334c8060bb94ee1"
Archived-At: 
 <https://www.postgresql.org/message-id/CADHFRcj_4uaxjNSJCCmB8LBE3w5BHh8a7u3pMpZiyDQSbawTyA%40mail.gmail.com>
Precedence: bulk

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

Thank you, Steve and Greg!

Your suggestions open up new potentials for me to explore.  At this moment,
I lean towards normalizing the database column values in upper case,
thereby out-sourcing the case-changing responsibility to the front end.  I
would love to hear from your thoughts on this pattern.

Cheers,

Bo

On Mon, Dec 4, 2023 at 11:39=E2=80=AFAM Steve Midgley <science@misuse.org> =
wrote:

>
>
> On Mon, Dec 4, 2023 at 10:09=E2=80=AFAM Greg Sabino Mullane <htamfids@gma=
il.com>
> wrote:
>
>> It's not clear exactly what you are trying to achieve, but you can use
>> Postgres' built-in text searching system to exclude stopwords. For examp=
le:
>>
>> CREATE FUNCTION initcap_realword(myword TEXT)
>>   returns TEXT language SQL AS
>> $$
>> SELECT CASE WHEN length(to_tsvector(myword)) < 1
>>   THEN myword ELSE initcap(myword) END;
>> $$;
>>
>> You could extend that to multi-word strings with a little effort.
>> However, knowing that macdonald should be MacDonald requires a lot more
>> intelligence than is provided by any Postgres built-in system or extensi=
on
>> that I know of. What you are looking at is the field of science known as
>> Natural Language Processing, which can get very complex very quickly. Bu=
t
>> for a Postgres answer, you might combine plpython3u with spacy (
>> https://spacy.io/usage/spacy-101).
>>
>> Cheers,
>> Greg
>>
>> I've been having some pretty good experiences with "hard" text
> transformations such as correct capitalization of names like MacDonald
> using GPT 3.5 Turbo API which is pretty cheap for the volume of data I've
> been working with.. Seems like Spacy might do similar things, and if it c=
an
> be run locally, might be much cheaper than a rental API..
>
> Steve
>

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

<div dir=3D"ltr"><div>Thank you, Steve and Greg!=C2=A0=C2=A0</div><div><br>=
</div><div>Your suggestions open up new potentials for me to explore.=C2=A0=
 At this moment, I lean towards normalizing the database column values in u=
pper case, thereby out-sourcing the case-changing responsibility to the fro=
nt end.=C2=A0 I would love to hear from your thoughts on this pattern.</div=
><div><br></div><div>Cheers,</div><div><br></div><div>Bo=C2=A0 </div></div>=
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon=
, Dec 4, 2023 at 11:39=E2=80=AFAM Steve Midgley &lt;<a href=3D"mailto:scien=
ce@misuse.org">science@misuse.org</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><br></di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On M=
on, Dec 4, 2023 at 10:09=E2=80=AFAM Greg Sabino Mullane &lt;<a href=3D"mail=
to:htamfids@gmail.com" target=3D"_blank">htamfids@gmail.com</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"=
>It&#39;s not clear exactly what you are trying to achieve, but you can use=
 Postgres&#39; built-in text searching system to exclude stopwords. For exa=
mple:<div><br></div><div>CREATE FUNCTION initcap_realword(myword TEXT)</div=
><div>=C2=A0 returns TEXT language SQL AS=C2=A0</div><div>$$</div><div>SELE=
CT CASE WHEN length(to_tsvector(myword)) &lt; 1</div><div>=C2=A0 THEN mywor=
d=C2=A0ELSE initcap(myword) END;</div><div>$$;<br></div><div><br></div><div=
>You could extend that to multi-word strings with a little effort. However,=
 knowing that macdonald should be MacDonald requires a lot more intelligenc=
e than is provided by any Postgres built-in system or extension that I know=
 of. What you are looking at is the field of science known as Natural Langu=
age Processing, which can get very complex very quickly. But for a Postgres=
 answer, you might combine plpython3u with spacy (<a href=3D"https://spacy.=
io/usage/spacy-101" target=3D"_blank">https://spacy.io/usage/spacy-101</a>)=
.</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div></div=
></blockquote><div>I&#39;ve been having some pretty good experiences with &=
quot;hard&quot; text transformations such as correct capitalization of name=
s like MacDonald using GPT 3.5 Turbo API which is pretty cheap for the volu=
me of data I&#39;ve been working with.. Seems like Spacy might do similar t=
hings, and if it can be run locally, might be much cheaper than a rental AP=
I..<br></div><div><br></div><div>Steve<br></div></div></div>
</blockquote></div>

--0000000000004334c8060bb94ee1--