Re: Fault with initcap - Mailing list 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'[\\[\\]\^\$\.\|\?\*\+\(\)\\~`\!@#%&\\-\\_+={}"<>:;, ]');
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