Re: Making substrings uppercase

On 9 Sep 2013, at 14:41, David Johnston <polobo(at)yahoo(dot)com> wrote:

> Oliver Kohll - Mailing Lists wrote
>> select regexp_replace(sentence,'\*(.*?)\*','' || upper('\1'),'g') from
>> sentences;
>
> Yeah, you cannot embed a function-call result in the "replace with" section;
> it has to be a literal (with the group insertion meta-sequences allowed of
> course).
>
> I see two possible approaches.
>
> 1) Use pl/perl (or some variant thereof) which has facilities to do just
> this.
> 2) Use regexp_matches(,,'g') to explode the input string into its components
> parts. You can explode it so every character of the original string is in
> the output with the different columns containing the "raw" and "to modify"
> parts of each match. This would be done in a sub-query and then in the
> parent query you would "string_agg(...)" the matches back together while
> manipulating the columns needed "i.e., string_agg(c1 || upper(c3))"
>
> HTH
>
> David J.
>

I see, I'm going with Perl, thanks.

Oliver