Thread: Making substrings uppercase

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
wouldfail if there was more than one word starred. 

Any other ideas?

Oliver

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.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Making-substrings-uppercase-tp5770096p5770108.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 9 Sep 2013, at 14:41, David Johnston <polobo@yahoo.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

Hi,

For special testing reason, I am trying to restore PG from a backup that the basebase is from Standby and WAL files are
fromMaster.  During recovery phase, for every WAL file process, it returned 'invalid resource manager ID in primary
checkpointrecord' and paused, I had to manually run "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set
PGto ignore this kind of errors. 

LOG:  database system was shut down at 2013-09-10 03:06:29 :  starting archive recovery
LOG:  restored log file "00000001000000B9000000CA" from archive
LOG:  invalid resource manager ID in primary checkpoint record
LOG:  invalid secondary checkpoint link in control file
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 17969) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
.
LOG:  restored log file "00000001000000B9000000CB" from archive
LOG:  invalid resource manager ID in primary checkpoint record
LOG:  invalid secondary checkpoint link in control file
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 17981) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
.
LOG:  restored log file "00000001000000B9000000CC" from archive
.
LOG:  restored log file "00000001000000B9000000CD" from archive


Please advise.
regards

Oliver Kohll - Mailing Lists wrote:
> 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.

Maybe you can turn that into a resultset, then uppercase individual
words, then join them back into a string.  Something like

select string_agg(case when words like '*%*' then upper(btrim(words, '*')) else words end, ' ')
from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy* dog', ' ') as words;

                  string_agg
----------------------------------------------
 The QUICK BROWN fox jumped over the LAZY dog
(1 fila)

This is a bit simplistic, but hopefully you get the idea.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi, any idea? can you  please advise?

On 10 Sep 2013, at 3:22 AM, ascot.moss@gmail.com wrote:

> Hi,
>
> For special testing reason, I am trying to restore PG from a backup that the basebase is from Standby and WAL files
arefrom Master.  During recovery phase, for every WAL file process, it returned 'invalid resource manager ID in primary
checkpointrecord' and paused, I had to manually run "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set
PGto ignore this kind of errors. 
>
> LOG:  database system was shut down at 2013-09-10 03:06:29 :  starting archive recovery
> LOG:  restored log file "00000001000000B9000000CA" from archive
> LOG:  invalid resource manager ID in primary checkpoint record
> LOG:  invalid secondary checkpoint link in control file
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 17969) was terminated by signal 6: Aborted
> LOG:  aborting startup due to startup process failure
> .
> LOG:  restored log file "00000001000000B9000000CB" from archive
> LOG:  invalid resource manager ID in primary checkpoint record
> LOG:  invalid secondary checkpoint link in control file
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 17981) was terminated by signal 6: Aborted
> LOG:  aborting startup due to startup process failure
> .
> LOG:  restored log file "00000001000000B9000000CC" from archive
> .
> LOG:  restored log file "00000001000000B9000000CD" from archive
>
>
> Please advise.
> regards
>
>
>
>

On Tue, Sep 10, 2013 at 5:51 AM, Oliver Kohll - Mailing Lists
<oliver.lists@gtwm.co.uk> wrote:
> On 9 Sep 2013, at 21:03, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> select string_agg(case when words like '*%*' then upper(btrim(words, '*'))
> else words end, ' ')
> from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy*
> dog', ' ') as words;
>
>                  string_agg
> ----------------------------------------------
> The QUICK BROWN fox jumped over the LAZY dog
>
>
> That's quite elegant. In the end I exported and used PERL, as some of my
> 'words' had spaces (they were ingredients like monosodium glutamate), but
> you could probably do a more complex regex in regexp_split_to_table to cope
> with that, or use pl/perl as previously suggested.

IMO, pl/perl is the way to go.  Being able to use postgres functions
to transform matched regex expressions would be just wonderful
although I wonder how fast it would be or if it's even possible.

merlin

Hi,

I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and pg-Slave with streaming replication.

The archive_command is enabled and the "rsync" is used in pg-Master to save all its archived WAL files to the 3rd
machinefor backup purpose,  by default, both fsync and wal_sync_method are commented out in postgresql.conf: 

archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq  %p pg@pg_slave:/usr/pg_arcxlog/%f'
#fsync = on                        # turns forced synchronization on or off
#wal_sync_method = fsync        # the default is the first option

Thus I think the latest WAL might not be flushed to disk from time to time in pg-Master, therefore the WAL saved in the
3rdserver might not contain the latest WAL. In order to avoid possible data lost, should 'fsync" be turned ON in
pg-Masterand pg-Slave?  if yes what would be the best settings of "wal_sync_method" for these two servers respectively? 

Please advise.
regards