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.

Ihor Romanchenko · Accepted Answer · 2013-09-09 14:05:52Z

2

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.

answered Sep 9, 2013 at 14:05

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oliver Kohll · Accepted Answer · 2013-09-09 13:51:37Z

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.

PPH · Accepted Answer · 2022-10-05 08:48:54Z

0

SQL version, something like this:

SELECT string_agg(m, '')
FROM (
    SELECT CASE WHEN n % 2 = 0 THEN upper(m) ELSE m END m
    FROM (
        SELECT m, row_number() OVER () n 
        FROM regexp_split_to_table( 'The *quick* *brown* fox j*ump*ed over the *lazy* dog' , '\*') m
    ) a
) a

split the string into a table with '*' and keep track of the row number
even rows should be UPPERized, if the first char is a '*', an empty row will be produced
glue all rows with string_agg

answered Oct 5, 2022 at 8:48

PPH

1381 silver badge6 bronze badges

Collectives™ on Stack Overflow

Replacing regex matched text with uppercase version in Postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related