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

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

On Mon, Dec 4, 2023 at 10:09=E2=80=AFAM Greg Sabino Mullane <htamfids@gmail=
.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 exampl=
e:
>
> 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 intelligen=
ce
> 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
> Language Processing, which can get very complex very quickly. But 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 can
be run locally, might be much cheaper than a rental API..

Steve

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Mon, Dec 4, 2023 at 10:09=E2=80=AF=
AM Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@g=
mail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft: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 ex=
clude stopwords. For example:<div><br></div><div>CREATE FUNCTION initcap_re=
alword(myword TEXT)</div><div>=C2=A0 returns TEXT language SQL AS=C2=A0</di=
v><div>$$</div><div>SELECT CASE WHEN length(to_tsvector(myword)) &lt; 1</di=
v><div>=C2=A0 THEN myword=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 require=
s a lot more intelligence than is provided by any Postgres built-in system =
or extension that I know of. What you are looking at is the field of scienc=
e known as Natural Language Processing, which can get very complex very qui=
ckly. 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 correc=
t capitalization of names like MacDonald using GPT 3.5 Turbo API which is p=
retty cheap for the volume of data I&#39;ve been working with.. Seems like =
Spacy might do similar things, and if it can be run locally, might be much =
cheaper than a rental API..<br></div><div><br></div><div>Steve<br></div></d=
iv></div>

--000000000000de62bf060bb36f57--