On Sat, Dec 10, 2022 at 6:32 AM Peter J. Holzer <hjp-pgsql@xxxxxx> 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.
