From: | Metin Ulusinan <metin(dot)ulusinan(at)ssicilian(dot)net> |
---|---|
To: | Shaozhong SHI <shishaozhong(at)gmail(dot)com> |
Cc: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Fault with initcap |
Date: | 2021-10-14 13:42:23 |
Message-ID: | CAPi93JT9JuUxjo-X0WbOEK9tEYDGnj9pCnXd_d38ALOQWwyTjw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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'[\\[\\]\^\$\.\|\?\*\+\(\)\\~`\!(at)#%&\\-\\_+={}"<>:;, ]');
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(at)gmail(dot)com>
wrote:
>
>
> On Wed, 13 Oct 2021 at 10:39, Metin Ulusinan <metin(dot)ulusinan(at)ssicilian(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Travers | 2021-10-14 14:22:56 | Re: JOB | DBA (Canada) |
Previous Message | Christoph Moench-Tegeder | 2021-10-14 13:28:37 | Re: Certificate validity error download.postgresql.org |
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Midgley | 2021-10-14 15:19:17 | Re: What is the regex for apostraphe in postgres? |
Previous Message | Torsten Grust | 2021-10-14 12:35:02 | Re: What is the regex for apostraphe in postgres? |