pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Regular expression for lower case to upper case.
Date: Sat, 10 Dec 2022 14:31:50 +0100
Message-ID: <20221210133150.4l57ps6lk5dcddd4@hjp.at> (raw)
In-Reply-To: <7EOY6xZE-t6VXu6ozkVJYGeHODc3-_PBubDAGaSXRP_JznkELzYdQJriJsWaIS2nTJ0Esyh0htfsdfEIUrnUUHh--58QQwTpo5y6wQKzrfU=@protonmail.com>
References: <yk4Ucm1Andim5urGdLDSwhTdytxgXWvHVd1PgUZht80zfEiK3Eo-Qz3YKFMI2D2cwhoLcNxY8mNoWbW_Plz65T6YdQpPSll1hPckh7WYeSA=@protonmail.com>
	<CAKFQuwbY3gxxtS9fYqDhog6SgOjx4jGE4s9JxZMcjO_YNosnZQ@mail.gmail.com>
	<7EOY6xZE-t6VXu6ozkVJYGeHODc3-_PBubDAGaSXRP_JznkELzYdQJriJsWaIS2nTJ0Esyh0htfsdfEIUrnUUHh--58QQwTpo5y6wQKzrfU=@protonmail.com>

On 2022-12-10 11:00:48 +0000, Eagna wrote:
> > RegExp by itself cannot do this. You have to match all parts of the
> > input into different capturing groups, then use lower() combined
> > with format() to build a new string. Putting the capturing groups
> > into an array is the most useful option.
> 
> OK - I *_kind_* of see what you're saying. 
> 
> There's a small fiddle here (https://dbfiddle.uk/rhw1AdBY) if you'd
> care to give an outline of the solution that you propose.

For example like this:

INSERT INTO test VALUES
('abc_def_ghi');

Let's say I want to uppercase the part between the two underscores.

First use regexp_replace to split the string into three parts: One
before the match, the match and one after the match:

SELECT
  regexp_replace(x, '(.*_)(.*)(_.*)', '\1'),
  regexp_replace(x, '(.*_)(.*)(_.*)', '\2'),
  regexp_replace(x, '(.*_)(.*)(_.*)', '\3')
FROM test;

╔════════════════╤════════════════╤════════════════╗
║ regexp_replace │ regexp_replace │ regexp_replace ║
╟────────────────┼────────────────┼────────────────╢
║ abc_           │ def            │ _ghi           ║
╚════════════════╧════════════════╧════════════════╝
(1 row)


Once that works, uppercase the part you want and concatenate everything
together again:

SELECT
  regexp_replace(x, '(.*_)(.*)(_.*)', '\1') ||
  upper(regexp_replace(x, '(.*_)(.*)(_.*)', '\2')) ||
  regexp_replace(x, '(.*_)(.*)(_.*)', '\3')
FROM test;

╔═════════════╗
║  ?column?   ║
╟─────────────╢
║ abc_DEF_ghi ║
╚═════════════╝
(1 row)

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (8+ messages)  latest in thread

Message-ID: <20221210133150.4l57ps6lk5dcddd4@hjp.at>
Permalink:  ../20221210133150.4l57ps6lk5dcddd4@hjp.at/
Also on:    postgresql.org/message-id/20221210133150.4l57ps6lk5dcddd4@hjp.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: hjp-pgsql@hjp.at
  Subject: Re: Regular expression for lower case to upper case.
  In-Reply-To: <20221210133150.4l57ps6lk5dcddd4@hjp.at>
  References: <yk4Ucm1Andim5urGdLDSwhTdytxgXWvHVd1PgUZht80zfEiK3Eo-Qz3YKFMI2D2cwhoLcNxY8mNoWbW_Plz65T6YdQpPSll1hPckh7WYeSA=@protonmail.com> <CAKFQuwbY3gxxtS9fYqDhog6SgOjx4jGE4s9JxZMcjO_YNosnZQ@mail.gmail.com> <7EOY6xZE-t6VXu6ozkVJYGeHODc3-_PBubDAGaSXRP_JznkELzYdQJriJsWaIS2nTJ0Esyh0htfsdfEIUrnUUHh--58QQwTpo5y6wQKzrfU=@protonmail.com> <20221210133150.4l57ps6lk5dcddd4@hjp.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox