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