From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Regular expression for lower case to upper case. |
Date: | 2022-12-10 13:31:50 |
Message-ID: | 20221210133150.4l57ps6lk5dcddd4@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Eagna | 2022-12-10 13:33:09 | Re: Regular expression to UPPER() a lower case string |
Previous Message | Peter J. Holzer | 2022-12-10 13:13:46 | Re: Regular expression to UPPER() a lower case string |