Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3zxQ-0008MH-VN for pgsql-general@arkaria.postgresql.org; Sat, 10 Dec 2022 13:32:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3zxP-0001f5-Ln for pgsql-general@arkaria.postgresql.org; Sat, 10 Dec 2022 13:31:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3zxP-0001ee-CQ for pgsql-general@lists.postgresql.org; Sat, 10 Dec 2022 13:31:59 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3zxJ-0006Dw-FV for pgsql-general@lists.postgresql.org; Sat, 10 Dec 2022 13:31:58 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id BEA737068; Sat, 10 Dec 2022 14:31:50 +0100 (CET) Date: Sat, 10 Dec 2022 14:31:50 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Regular expression for lower case to upper case. Message-ID: <20221210133150.4l57ps6lk5dcddd4@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <7EOY6xZE-t6VXu6ozkVJYGeHODc3-_PBubDAGaSXRP_JznkELzYdQJriJsWaIS2nTJ0Esyh0htfsdfEIUrnUUHh--58QQwTpo5y6wQKzrfU=@protonmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="lny2zqbxegqcdnpf" Content-Disposition: inline In-Reply-To: <7EOY6xZE-t6VXu6ozkVJYGeHODc3-_PBubDAGaSXRP_JznkELzYdQJriJsWaIS2nTJ0Esyh0htfsdfEIUrnUUHh--58QQwTpo5y6wQKzrfU=@protonmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --lny2zqbxegqcdnpf Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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. >=20 > OK - I *_kind_* of see what you're saying.=20 >=20 > 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; =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=A4=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=A4=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=97 =E2=95=91 regexp_replace =E2=94=82 regexp_replace =E2=94=82 regexp_replace = =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=95=A2 =E2=95=91 abc_ =E2=94=82 def =E2=94=82 _ghi = =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=A7=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=A7=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=9D (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; =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97 =E2=95=91 ?column? =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2 =E2=95=91 abc_DEF_ghi =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D (1 row) hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --lny2zqbxegqcdnpf Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmOUikEACgkQ8g5IURL+ KF0fCw/+MmLO+efoSabfHskjdnWBSFrMKnm+UYzlZUF5vzv72v2C2VPrEgOMTPG6 P9cfEgc5VgllRSjH7UDZi1wRD+Wod7MMaGd3OTMQXlYVMV1NeLKccXn4nE/5jVGL z+27AwYMi8MVU/eG51asSmUZxVajRLrLJmUQqV7EMsUVCcg3hvxtYXxKdA+pU7pO jbx2Q3UD54fGudXTU+fkkNsmSYusgY0tlp+QAGAS1hRvUA+GZetljs67mwhA4QUs GgeJuYKL65ABRisnFVbTt25HbI62ZT+9B+x2yYUTCwKUGvNQFMjcA+mejVsm+89w XR4C5gujFTKjFK2fxPCuzNa3mtiAHhCExpEEs+ZsOR+3CggpmovlB9b4ASTqSiJ9 GF99J2Ek2V+PKTThoReCuOvlzqLDfjUqp5AXYcNVyUlR26sTzyu3LXLvk419a+r5 cHicBgQhMafj89l3FMyJ3kNNdFcycWIBgtMz6e7q3QftK3IQ1yf0Yr6aSR57AVJ/ c0qSkVXeSdfTMvGkUolfDdQuIYRJ1UuNO/sV1Me3XRi6g6wGFquJNukLxiRgeXjp z2PMwX11SblmWh86t24h+bpFHhdqGoLzYeNVh7fYW91kTkz93rx4Qx2T16kGDNNm QTRBt2Vbr87sV3Ywz0l/jikZvar4K3HOLvd6GsXmBCtuuhpfNvs= =1ecz -----END PGP SIGNATURE----- --lny2zqbxegqcdnpf--