Re: Overcoming Initcap Function limitations?

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 AM Steve Midgley <science(at)misuse(dot)org> wrote:

>
>
> On Mon, Dec 4, 2023 at 10:09 AM Greg Sabino Mullane <htamfids(at)gmail(dot)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 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
>>
>> 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
>