Replacing regex matched text with uppercase version in Postgresql

Question

Given a string with certain words surrounded by stars, e.g.

The *quick* *brown* fox jumped over the *lazy* dog

can you transform the words surrounded by stars into uppercase versions, i.e.

The QUICK BROWN fox jumped over the LAZY dog

Given text in a column 'sentence' in table 'sentences', I can mark/extract the words as follows:

SELECT regexp_replace(sentence,'\*(.*?)\*','STARTUPPER\1ENDUPPER','g') FROM sentences;

but my first attempt at uppercase transforms doesn't work:

select regexp_replace(sentence,'\*(.*?)\*','' || upper('\1'),'g') from sentences;

I thought of using substring() to split the parts up after replacing the stars with start and end markers, but that would fail if there was more than one word starred.

Answer 1

You can create a PL/pgSQL function like:

CREATE FUNCTION upper_asterisk(inp_str varchar)
RETURNS varchar AS $$
DECLARE t_str varchar;
BEGIN

  FOR t_str IN (SELECT regexp_matches(inp_str,'\*.+\*','g'))
  BEGIN
    inp_str := replace(inp_str, t_str, upper(t_str));
  END;

  RETURN inp_str;
END;
$$  LANGUAGE plpgsql;

(Havent tested, may have bugs).

Or use any available language to write such function inside DB.

Answer 2

Answer from the Postgresql mailing list:

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.