From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Regular expression for lower case to upper case. |
Date: | 2022-12-10 16:46:50 |
Message-ID: | CAKFQuwYezRd_YJKMJAeycnDEAM=mE6cJb2Gn=R2aLmSpqeSuXw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Dec 10, 2022 at 6:32 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
> 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;
>
A bit too inefficient for my taste.
I was describing the following:
with parts as materialized (
select regexp_match(
'abc_def_ghi',
'^([^_]*_)([^_]*_)([^_]*)$') as part_array
)
select format(
'%s%s%s',
part_array[1],
upper(part_array[2]),
part_array[3])
from parts;
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2022-12-11 10:41:06 | Re: Regular expression for lower case to upper case. |
Previous Message | Peter J. Holzer | 2022-12-10 16:16:06 | Re: Regular expression to UPPER() a lower case string |