Making substrings uppercase

From: Oliver Kohll - Mailing Lists <oliver(dot)lists(at)gtwm(dot)co(dot)uk>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Making substrings uppercase
Date: 2013-09-09 11:34:47
Message-ID: 85C3914E-5119-4332-81BD-5C4F0C9DB91E@gtwm.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

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 with 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.

Any other ideas?

Oliver

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marc Mamin 2013-09-09 12:12:29 Re: Sum of columns
Previous Message 高健 2013-09-09 10:07:19 PostgreSQL and cgroup