Re: Fault with initcap

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