MIME-Version: 1.0
References: 
 <CADHFRch1=ND1TpiOidJELBdJJSugn-H1d3vyjg9gh9Nw1dZCgg@mail.gmail.com>
In-Reply-To: 
 <CADHFRch1=ND1TpiOidJELBdJJSugn-H1d3vyjg9gh9Nw1dZCgg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 4 Dec 2023 13:08:03 -0500
Message-ID: 
 <CAKAnmmKhepxPzLWiU2JMNWsNiwLmQAyKCgV-f2dMot1hKVa-hA@mail.gmail.com>
Subject: Re: Overcoming Initcap Function limitations?
To: Bo Guo <bo.guo@gisticinc.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f02fe5060bb30142"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmKhepxPzLWiU2JMNWsNiwLmQAyKCgV-f2dMot1hKVa-hA%40mail.gmail.com>
Precedence: bulk

--000000000000f02fe5060bb30142
Content-Type: text/plain; charset="UTF-8"

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 example:

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 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

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

<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 st=
opwords. For example:<div><br></div><div>CREATE FUNCTION initcap_realword(m=
yword TEXT)</div><div>=C2=A0 returns TEXT language SQL AS=C2=A0</div><div>$=
$</div><div>SELECT CASE WHEN length(to_tsvector(myword)) &lt; 1</div><div>=
=C2=A0 THEN myword=C2=A0ELSE initcap(myword) END;</div><div>$$;<br></div><d=
iv><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 intelligence than is provided by any Postgres built-in system or exte=
nsion 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. B=
ut for a Postgres answer, you might combine plpython3u with spacy (<a href=
=3D"https://spacy.io/usage/spacy-101">https://spacy.io/usage/spacy-101</a>)=
.</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div></div=
>

--000000000000f02fe5060bb30142--