agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFault with initcap
19+ messages / 9 participants
[nested] [flat]
* Fault with initcap
@ 2021-10-12 16:30 Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 3 replies; 19+ messages in thread
From: Shaozhong SHI @ 2021-10-12 16:30 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
I tried initcap and found a major problem with it.
Initcap of notemachine is NoteMachine.
Initcap of Sainsbury's Bank is Sainsbury'S bank.
This is not expected.
Anyway to get around this problem?
Regards,
David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Fault with initcap
@ 2021-10-12 16:31 Shaozhong SHI <shishaozhong@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Shaozhong SHI @ 2021-10-12 16:31 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
I tried initcap and found a major problem with it.
Initcap of notemachine is NoteMachine.
Initcap of Sainsbury's Bank is Sainsbury'S bank.
This is not expected.
Anyway to get around this problem?
Regards,
David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 16:53 Rob Sargent <robjsargent@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Rob Sargent @ 2021-10-12 16:53 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
> On Oct 12, 2021, at 10:30 AM, Shaozhong SHI <shishaozhong@gmail.com> wrote:
>
> I tried initcap and found a major problem with it.
>
> Initcap of notemachine is NoteMachine.
My version 12 system gives Notemachine
>
> Initcap of Sainsbury's Bank is Sainsbury'S bank.
>
> This is not expected.
>
> Anyway to get around this problem?
>
> Regards,
>
> David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 17:28 Steve Midgley <science@misuse.org>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Steve Midgley @ 2021-10-12 17:28 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
On Tue, Oct 12, 2021 at 9:54 AM Rob Sargent <robjsargent@gmail.com> wrote:
>
>
> > On Oct 12, 2021, at 10:30 AM, Shaozhong SHI <shishaozhong@gmail.com>
> wrote:
> >
> > I tried initcap and found a major problem with it.
> >
> > Initcap of notemachine is NoteMachine.
> My version 12 system gives Notemachine
> >
> > Initcap of Sainsbury's Bank is Sainsbury'S bank.
> >
> > This is not expected.
> >
> > Anyway to get around this problem?
> >
>
>
Pg v.13 doesn't have a problem with notemachine either, but does seem to
screw up Sainsbury's Bank as Sainsbury'S bank.
https://www.db-fiddle.com/f/m2BMATwVcp6A1ZvD8TyurW/1
The only way I could think of fixing this would be to swap out the
apostrophe with an alpha string not present in the string, and then swap
back when you're done (see example above -- I'm not recommending using
"xyzxyz" - that's just an example. And ideally you'd search for your
placeholder string first to be sure it doesn't exist, or escape it).
Or just use a regexp to solve your casing problem -- maybe break up your
string by whitespace into array elements, uppercase the first letter of
each array and the concat the array elements back?
Steve
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 19:34 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Adrian Klaver @ 2021-10-12 19:34 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 10/12/21 09:31, Shaozhong SHI wrote:
> I tried initcap and found a major problem with it.
What Postgres version?
In version 12 and 14 I get:
>
> Initcap of notemachine is NoteMachine.
select initcap('notemachine');
initcap
-------------
Notemachine
>
> Initcap of Sainsbury's Bank is Sainsbury'S bank.
select initcap('Sainsbury''s Bank');
initcap
------------------
Sainsbury'S Bank
Which follows the definition here:
https://www.postgresql.org/docs/14/functions-string.html
initcap ( text ) → text
Converts the first letter of each word to upper case and the rest to
lower case. Words are sequences of alphanumeric characters separated by
non-alphanumeric characters.
>
> This is not expected.
What is the encoding, collate, ctype for the database?
Can be found in psql using:
\l db_name
>
> Anyway to get around this problem?
>
> Regards,
>
> David
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 20:50 Shaozhong SHI <shishaozhong@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 19+ messages in thread
From: Shaozhong SHI @ 2021-10-12 20:50 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, 12 Oct 2021 at 20:34, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 10/12/21 09:31, Shaozhong SHI wrote:
> > I tried initcap and found a major problem with it.
>
> What Postgres version?
>
> In version 12 and 14 I get:
>
> >
> > Initcap of notemachine is NoteMachine.
>
> select initcap('notemachine');
> initcap
> -------------
> Notemachine
>
> >
> > Initcap of Sainsbury's Bank is Sainsbury'S bank.
>
> select initcap('Sainsbury''s Bank');
> initcap
> ------------------
> Sainsbury'S Bank
>
>
> Which follows the definition here:
>
> https://www.postgresql.org/docs/14/functions-string.html
>
> initcap ( text ) → text
>
> Converts the first letter of each word to upper case and the rest to
> lower case. Words are sequences of alphanumeric characters separated by
> non-alphanumeric characters.
>
> Hi, Adrian Klaver,
>
It looks like that you replicated the error.
There must be a way to do the following.
a column contains a list of words. Only the first letter of each word
should be capitalised. INITCAP can not do that. How to create a function
just to capitalised each word (substring) in a list of words/strings. This
will be very useful and create great impact.
Regards,
David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 21:09 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Shaozhong SHI <shishaozhong@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Karsten Hilbert @ 2021-10-12 21:09 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Am Tue, Oct 12, 2021 at 09:50:16PM +0100 schrieb Shaozhong SHI:
> There must be a way to do the following.
>
> [...] Only the first letter of each word should be capitalised.
Indeed, there is. It is called "human brain in cultural
context". "AI" is close nowadays, but, hopefully, not quite
there yet.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 22:02 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Adrian Klaver @ 2021-10-12 22:02 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On 10/12/21 13:50, Shaozhong SHI wrote:
>
>
> On Tue, 12 Oct 2021 at 20:34, Adrian Klaver <adrian.klaver@aklaver.com
>
> Which follows the definition here:
>
> https://www.postgresql.org/docs/14/functions-string.html
> <https://www.postgresql.org/docs/14/functions-string.html;
>
> initcap ( text ) → text
>
> Converts the first letter of each word to upper case and the rest to
> lower case. Words are sequences of alphanumeric characters separated by
> non-alphanumeric characters.
>
> Hi, Adrian Klaver,
>
>
> It looks like that you replicated the error.
There is no error, initcap is doing what it is documented to.
notemachine is not two words anymore then 'online', 'bluebell',
'network' are.
>
> There must be a way to do the following.
Maybe, but as Karsten says it would involve an AI. One that understands
the mutt language that is English.
>
> a column contains a list of words. Only the first letter of each word
> should be capitalised. INITCAP can not do that. How to create a
> function just to capitalised each word (substring) in a list of
> words/strings. This will be very useful and create great impact.
From here:
https://www.grammarly.com/blog/14-of-the-longest-words-in-english/
uncopyrightable
where would you split that into words?:
Some 'words' I see:
un
unc
copy
copyright
right
table
able
>
> Regards,
>
> David
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-12 23:03 Shaozhong SHI <shishaozhong@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 19+ messages in thread
From: Shaozhong SHI @ 2021-10-12 23:03 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, 12 Oct 2021 at 23:02, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 10/12/21 13:50, Shaozhong SHI wrote:
> >
> >
> > On Tue, 12 Oct 2021 at 20:34, Adrian Klaver <adrian.klaver@aklaver.com
>
> >
> > Which follows the definition here:
> >
> > https://www.postgresql.org/docs/14/functions-string.html
> > <https://www.postgresql.org/docs/14/functions-string.html;
> >
> > initcap ( text ) → text
> >
> > Converts the first letter of each word to upper case and the rest to
> > lower case. Words are sequences of alphanumeric characters separated
> by
> > non-alphanumeric characters.
> >
> > Hi, Adrian Klaver,
> >
> >
> > It looks like that you replicated the error.
>
> There is no error, initcap is doing what it is documented to.
>
> notemachine is not two words anymore then 'online', 'bluebell',
> 'network' are.
>
>
> >
> > There must be a way to do the following.
>
> Maybe, but as Karsten says it would involve an AI. One that understands
> the mutt language that is English.
>
> >
> > a column contains a list of words. Only the first letter of each word
> > should be capitalised. INITCAP can not do that. How to create a
> > function just to capitalised each word (substring) in a list of
> > words/strings. This will be very useful and create great impact.
>
> From here:
>
> https://www.grammarly.com/blog/14-of-the-longest-words-in-english/
>
> uncopyrightable
>
> where would you split that into words?:
>
> Some 'words' I see:
>
> un
> unc
> copy
> copyright
> right
> table
> able
>
> >
> > Regards,
> >
> > David
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
Hi, Adrian Klaver,
In Python, there is a capwords. Do we have an equivalent in Postgres?
Regards, David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-13 00:27 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Adrian Klaver @ 2021-10-13 00:27 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On 10/12/21 16:03, Shaozhong SHI wrote:
>
>
>
> Hi, Adrian Klaver,
>
> In Python, there is a capwords. Do we have an equivalent in Postgres?
https://docs.python.org/3/library/string.html?highlight=capwords#string.capwords
string.capwords(s, sep=None)
Split the argument into words using str.split(), capitalize each
word using str.capitalize(), and join the capitalized words using
str.join(). If the optional second argument sep is absent or None, runs
of whitespace characters are replaced by a single space and leading and
trailing whitespace are removed, otherwise sep is used to split and join
the words.
That is not going to do what you are proposing either as it still comes
down to deciding where to split the 'words':
import string
string.capwords('notemachine')
'Notemachine'
There are similar functions to split strings here:
https://www.postgresql.org/docs/14/functions-string.html
Though again they depend on some delimiter or regexp to make the split.
There is no function that just 'knows' that 'notemachine' is two words
and should become 'NoteMachine'.
> Regards, David
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-13 03:55 Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 19+ messages in thread
From: Lee Hachadoorian @ 2021-10-13 03:55 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On Tue, Oct 12, 2021 at 8:28 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
>
> There is no function that just 'knows' that 'notemachine' is two words
> and should become 'NoteMachine'.
>
Any chance that 'notemachine' is stored with a zero-width space (Unicode
U+200B)? This is common for compound words where the programmer wants to be
able to "know" that they are distinct words that are smashed together.
select initcap('notemachine'), initcap('note' || U&'\200B' || 'machine');
initcap |initcap |
-----------+------------+
Notemachine|NoteMachine|
You could check the length() of the string. If it is 12, there is an
invisible character in there. This would explain the "unexpected" behavior
that no one seems to be able to replicate.
Best,
--Lee
--
Lee Hachadoorian
Assistant Professor of Instruction, Geography and Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-13 06:33 Metin Ulusinan <metin.ulusinan@ssicilian.net>
parent: Shaozhong SHI <shishaozhong@gmail.com>
2 siblings, 2 replies; 19+ messages in thread
From: Metin Ulusinan @ 2021-10-13 06:33 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
I tried both on PostgreSQL 11.13 and yes "Sainsbury's bank" 's "S" is
buggy, but notemachine is not.
Will anyone try @ 14?
n s version
Notemachine Sainsbury'S Bank PostgreSQL 11.13 (Debian 11.13-0+deb10u1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
On Tue, Oct 12, 2021 at 7:30 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
> I tried initcap and found a major problem with it.
>
> Initcap of notemachine is NoteMachine.
>
> Initcap of Sainsbury's Bank is Sainsbury'S bank.
>
> This is not expected.
>
> Anyway to get around this problem?
>
> Regards,
>
> David
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-13 07:03 Pavel Stehule <pavel.stehule@gmail.com>
parent: Metin Ulusinan <metin.ulusinan@ssicilian.net>
1 sibling, 0 replies; 19+ messages in thread
From: Pavel Stehule @ 2021-10-13 07:03 UTC (permalink / raw)
To: Metin Ulusinan <metin.ulusinan@ssicilian.net>; +Cc: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
st 13. 10. 2021 v 8:34 odesílatel Metin Ulusinan <
metin.ulusinan@ssicilian.net> napsal:
> I tried both on PostgreSQL 11.13 and yes "Sainsbury's bank" 's "S" is
> buggy, but notemachine is not.
>
> Will anyone try @ 14?
>
It cannot to work everywhere - the word is defined (for this function) as
sequence of alpha numeric chars. "'" is not alpha numeric char.
Theoretically it can work if you use ICU collation provider, if it is
supported by ICU.
Regards
Pavel
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-13 08:48 Shaozhong SHI <shishaozhong@gmail.com>
parent: Metin Ulusinan <metin.ulusinan@ssicilian.net>
1 sibling, 1 reply; 19+ messages in thread
From: Shaozhong SHI @ 2021-10-13 08:48 UTC (permalink / raw)
To: Metin Ulusinan <metin.ulusinan@ssicilian.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Wed, 13 Oct 2021 at 07:33, Metin Ulusinan <metin.ulusinan@ssicilian.net>
wrote:
> I tried both on PostgreSQL 11.13 and yes "Sainsbury's bank" 's "S" is
> buggy, but notemachine is not.
>
> Will anyone try @ 14?
>
>
> n s version
> Notemachine Sainsbury'S Bank PostgreSQL 11.13 (Debian 11.13-0+deb10u1) on
> x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
>
>
> Hello, Metin Ulusinan,
>
I can confirm that you pin-out the issue.
There is a INITCAP2. Alternative function to PostgreSQL builtin
initcap(text) with support for accented words. · GitHub
<https://gist.github.com/pv8/8291531;
Can this be adapted to capitalise the first letter of each work in a string?
Regards,
David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-13 09:38 Metin Ulusinan <metin.ulusinan@ssicilian.net>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Metin Ulusinan @ 2021-10-13 09:38 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi,
Yes, this can be adaptable, and i did simple version of this.
It just split text words with find spaces, capitalise each
pieces(word) and merge together again.
This is a quick and simple work. You can develop over it about your needs.
Try that and tell us about result.
CREATE OR REPLACE FUNCTION initcap2(text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
sentence TEXT := '';
word_array TEXT[];
word TEXT;
word_out TEXT;
BEGIN
sentence := $1;
IF sentence is NULL THEN
RETURN NULL;
END IF;
word_array := regexp_split_to_array($1, E'\\s+');
FOREACH word IN ARRAY word_array
LOOP
word_out := upper(left(word, 1)) || lower(substring(word, 2));
sentence := regexp_replace(sentence, word, word_out);
END LOOP;
RETURN trim(sentence);
END;
$function$
;
On Wed, Oct 13, 2021 at 11:48 AM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
>
>
> On Wed, 13 Oct 2021 at 07:33, Metin Ulusinan <metin.ulusinan@ssicilian.net>
> wrote:
>
>> I tried both on PostgreSQL 11.13 and yes "Sainsbury's bank" 's "S" is
>> buggy, but notemachine is not.
>>
>> Will anyone try @ 14?
>>
>>
>> n s version
>> Notemachine Sainsbury'S Bank PostgreSQL 11.13 (Debian 11.13-0+deb10u1)
>> on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
>>
>>
>> Hello, Metin Ulusinan,
>>
>
> I can confirm that you pin-out the issue.
>
> There is a INITCAP2. Alternative function to PostgreSQL builtin
> initcap(text) with support for accented words. · GitHub
> <https://gist.github.com/pv8/8291531;
>
> Can this be adapted to capitalise the first letter of each work in a
> string?
>
> Regards,
>
> David
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Fault with initcap
@ 2021-10-13 16:17 Jain, Ankit <Ankit.Jain@snapon.com>
parent: Metin Ulusinan <metin.ulusinan@ssicilian.net>
1 sibling, 0 replies; 19+ messages in thread
From: Jain, Ankit @ 2021-10-13 16:17 UTC (permalink / raw)
To: Metin Ulusinan <metin.ulusinan@ssicilian.net>; Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
This was reported in PG 8.1.4 15 years ago - https://pgsql-bugs.postgresql.narkive.com/R7HmTKef/bug-2579-initcap-should-not-capitalize-letter-aft...
From: Metin Ulusinan <metin.ulusinan@ssicilian.net>
Sent: Wednesday, October 13, 2021 5:39 AM
To: Shaozhong SHI <shishaozhong@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Fault with initcap
CAUTION: This email originated from outside of Snap-on. Do not click on links or open attachments unless you have validated the sender, even if it is a known contact. Contact the sender by phone to validate the contents.
Hi,
Yes, this can be adaptable, and i did simple version of this.
It just split text words with find spaces, capitalise each
pieces(word) and merge together again.
This is a quick and simple work. You can develop over it about your needs.
Try that and tell us about result.
CREATE OR REPLACE FUNCTION initcap2(text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
sentence TEXT := '';
word_array TEXT[];
word TEXT;
word_out TEXT;
BEGIN
sentence := $1;
IF sentence is NULL THEN
RETURN NULL;
END IF;
word_array := regexp_split_to_array($1, E'\\s+');
FOREACH word IN ARRAY word_array
LOOP
word_out := upper(left(word, 1)) || lower(substring(word, 2));
sentence := regexp_replace(sentence, word, word_out);
END LOOP;
RETURN trim(sentence);
END;
$function$
;
On Wed, Oct 13, 2021 at 11:48 AM Shaozhong SHI <shishaozhong@gmail.com<mailto:shishaozhong@gmail.com>> wrote:
On Wed, 13 Oct 2021 at 07:33, Metin Ulusinan <metin.ulusinan@ssicilian.net<mailto:metin.ulusinan@ssicilian.net>> wrote:
I tried both on PostgreSQL 11.13 and yes "Sainsbury's bank" 's "S" is buggy, but notemachine is not.
Will anyone try @ 14?
n
s
version
Notemachine
Sainsbury'S Bank
PostgreSQL 11.13 (Debian 11.13-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Hello, Metin Ulusinan,
I can confirm that you pin-out the issue.
There is a INITCAP2. Alternative function to PostgreSQL builtin initcap(text) with support for accented words. · GitHub<https://urldefense.com/v3/__https:/gist.github.com/pv8/8291531__;!!Lf_9VycLqA!yapR_3tOx6q4nbC3ZDtCTz...;
Can this be adapted to capitalise the first letter of each work in a string?
Regards,
David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-14 08:55 Shaozhong SHI <shishaozhong@gmail.com>
parent: Metin Ulusinan <metin.ulusinan@ssicilian.net>
1 sibling, 1 reply; 19+ messages in thread
From: Shaozhong SHI @ 2021-10-14 08:55 UTC (permalink / raw)
To: Metin Ulusinan <metin.ulusinan@ssicilian.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Wed, 13 Oct 2021 at 10:39, Metin Ulusinan <metin.ulusinan@ssicilian.net>
wrote:
> Hi,
> Yes, this can be adaptable, and i did simple version of this.
> It just split text words with find spaces, capitalise each
> pieces(word) and merge together again.
> This is a quick and simple work. You can develop over it about your needs.
>
> Try that and tell us about result.
>
>
> CREATE OR REPLACE FUNCTION initcap2(text)
> RETURNS text
> LANGUAGE plpgsql
> AS $function$
> DECLARE
> sentence TEXT := '';
> word_array TEXT[];
> word TEXT;
> word_out TEXT;
> BEGIN
> sentence := $1;
>
> IF sentence is NULL THEN
> RETURN NULL;
> END IF;
>
> word_array := regexp_split_to_array($1, E'\\s+');
> FOREACH word IN ARRAY word_array
> LOOP
> word_out := upper(left(word, 1)) || lower(substring(word, 2));
> sentence := regexp_replace(sentence, word, word_out);
> END LOOP;
>
> RETURN trim(sentence);
> END;
> $function$
> ;
>
> Hello, Metin,
>
See the following testing response.
ERROR: invalid regular expression: parentheses () not balanced CONTEXT:
PL/pgSQL function testinitcap2(text) line 18 at assignment
SQL state: 2201B
Regards,
David
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-10-14 13:42 Metin Ulusinan <metin.ulusinan@ssicilian.net>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Metin Ulusinan @ 2021-10-14 13:42 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Problem is about with parantesheses in text
and real problem is non alphanumeric chars.
Different samples will cause different problems.
We don't know about your data and samples.
But i changed the code again from original one.
I deleted " ' " char in regexp function code.
It seems better than before.
CREATE OR REPLACE FUNCTION public.initcap2(text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
sentence TEXT := '';
word_array TEXT[];
word TEXT;
word_out TEXT;
BEGIN
sentence := $1;
IF sentence is NULL THEN
RETURN NULL;
END IF;
word_array := regexp_split_to_array($1,
E'[\\[\\]\^\$\.\|\?\*\+\(\)\\~`\!@#%&\\-\\_+={}"<>:;, ]');
FOREACH word IN ARRAY word_array
LOOP
word_out := upper(left(word, 1)) || lower(substring(word, 2));
sentence := replace(sentence, word, word_out);
END LOOP;
RETURN trim(sentence);
END;
$function$
;
On Thu, Oct 14, 2021 at 11:55 AM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
>
>
> On Wed, 13 Oct 2021 at 10:39, Metin Ulusinan <metin.ulusinan@ssicilian.net>
> wrote:
>
>> Hi,
>> Yes, this can be adaptable, and i did simple version of this.
>> It just split text words with find spaces, capitalise each
>> pieces(word) and merge together again.
>> This is a quick and simple work. You can develop over it about your needs.
>>
>> Try that and tell us about result.
>>
>>
>> CREATE OR REPLACE FUNCTION initcap2(text)
>> RETURNS text
>> LANGUAGE plpgsql
>> AS $function$
>> DECLARE
>> sentence TEXT := '';
>> word_array TEXT[];
>> word TEXT;
>> word_out TEXT;
>> BEGIN
>> sentence := $1;
>>
>> IF sentence is NULL THEN
>> RETURN NULL;
>> END IF;
>>
>> word_array := regexp_split_to_array($1, E'\\s+');
>> FOREACH word IN ARRAY word_array
>> LOOP
>> word_out := upper(left(word, 1)) || lower(substring(word, 2));
>> sentence := regexp_replace(sentence, word, word_out);
>> END LOOP;
>>
>> RETURN trim(sentence);
>> END;
>> $function$
>> ;
>>
>> Hello, Metin,
>>
>
> See the following testing response.
>
> ERROR: invalid regular expression: parentheses () not balanced CONTEXT:
> PL/pgSQL function testinitcap2(text) line 18 at assignment
> SQL state: 2201B
>
> Regards,
>
> David
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Fault with initcap
@ 2021-11-30 00:17 Shaozhong SHI <shishaozhong@gmail.com>
parent: Metin Ulusinan <metin.ulusinan@ssicilian.net>
0 siblings, 0 replies; 19+ messages in thread
From: Shaozhong SHI @ 2021-11-30 00:17 UTC (permalink / raw)
To: Metin Ulusinan <metin.ulusinan@ssicilian.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
It is better to reveal the content of initcap.
But, \sf+ initcap and etc. did not reveal.
Regards,
David
On Thu, 14 Oct 2021 at 14:42, Metin Ulusinan <metin.ulusinan@ssicilian.net>
wrote:
> Problem is about with parantesheses in text
> and real problem is non alphanumeric chars.
> Different samples will cause different problems.
> We don't know about your data and samples.
>
> But i changed the code again from original one.
> I deleted " ' " char in regexp function code.
> It seems better than before.
>
>
>
> CREATE OR REPLACE FUNCTION public.initcap2(text)
> RETURNS text
> LANGUAGE plpgsql
> AS $function$
> DECLARE
> sentence TEXT := '';
> word_array TEXT[];
> word TEXT;
> word_out TEXT;
> BEGIN
> sentence := $1;
>
> IF sentence is NULL THEN
> RETURN NULL;
> END IF;
>
> word_array := regexp_split_to_array($1,
> E'[\\[\\]\^\$\.\|\?\*\+\(\)\\~`\!@#%&\\-\\_+={}"<>:;, ]');
> FOREACH word IN ARRAY word_array
> LOOP
> word_out := upper(left(word, 1)) || lower(substring(word, 2));
> sentence := replace(sentence, word, word_out);
> END LOOP;
>
> RETURN trim(sentence);
> END;
> $function$
> ;
>
>
> On Thu, Oct 14, 2021 at 11:55 AM Shaozhong SHI <shishaozhong@gmail.com>
> wrote:
>
>>
>>
>> On Wed, 13 Oct 2021 at 10:39, Metin Ulusinan <
>> metin.ulusinan@ssicilian.net> wrote:
>>
>>> Hi,
>>> Yes, this can be adaptable, and i did simple version of this.
>>> It just split text words with find spaces, capitalise each
>>> pieces(word) and merge together again.
>>> This is a quick and simple work. You can develop over it about your
>>> needs.
>>>
>>> Try that and tell us about result.
>>>
>>>
>>> CREATE OR REPLACE FUNCTION initcap2(text)
>>> RETURNS text
>>> LANGUAGE plpgsql
>>> AS $function$
>>> DECLARE
>>> sentence TEXT := '';
>>> word_array TEXT[];
>>> word TEXT;
>>> word_out TEXT;
>>> BEGIN
>>> sentence := $1;
>>>
>>> IF sentence is NULL THEN
>>> RETURN NULL;
>>> END IF;
>>>
>>> word_array := regexp_split_to_array($1, E'\\s+');
>>> FOREACH word IN ARRAY word_array
>>> LOOP
>>> word_out := upper(left(word, 1)) || lower(substring(word, 2));
>>> sentence := regexp_replace(sentence, word, word_out);
>>> END LOOP;
>>>
>>> RETURN trim(sentence);
>>> END;
>>> $function$
>>> ;
>>>
>>> Hello, Metin,
>>>
>>
>> See the following testing response.
>>
>> ERROR: invalid regular expression: parentheses () not balanced CONTEXT:
>> PL/pgSQL function testinitcap2(text) line 18 at assignment
>> SQL state: 2201B
>>
>> Regards,
>>
>> David
>>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2021-11-30 00:17 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-12 16:30 Fault with initcap Shaozhong SHI <shishaozhong@gmail.com>
2021-10-12 16:31 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-12 19:34 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-10-12 20:50 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-12 21:09 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2021-10-12 22:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-10-12 23:03 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-13 00:27 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-10-13 03:55 ` Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
2021-10-12 16:53 ` Rob Sargent <robjsargent@gmail.com>
2021-10-12 17:28 ` Steve Midgley <science@misuse.org>
2021-10-13 06:33 ` Metin Ulusinan <metin.ulusinan@ssicilian.net>
2021-10-13 07:03 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-10-13 08:48 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-13 09:38 ` Metin Ulusinan <metin.ulusinan@ssicilian.net>
2021-10-13 16:17 ` Jain, Ankit <Ankit.Jain@snapon.com>
2021-10-14 08:55 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-14 13:42 ` Metin Ulusinan <metin.ulusinan@ssicilian.net>
2021-11-30 00:17 ` Shaozhong SHI <shishaozhong@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox