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 1p40Cg-00017M-M5 for pgsql-general@arkaria.postgresql.org; Sat, 10 Dec 2022 13:47:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p40Cf-0000Ke-Ha for pgsql-general@arkaria.postgresql.org; Sat, 10 Dec 2022 13:47:45 +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 1p40Cf-0000KV-7O for pgsql-general@lists.postgresql.org; Sat, 10 Dec 2022 13:47:45 +0000 Received: from mail.thenautilus.net ([178.162.154.203]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p40Cc-0006Mp-RE for pgsql-general@lists.postgresql.org; Sat, 10 Dec 2022 13:47:44 +0000 Received: (qmail 11753 invoked from network); 10 Dec 2022 13:47:41 -0000 Received: from unknown (HELO nautilus) (dakkar@::ffff:81.2.107.215) by 0 with ESMTPA; 10 Dec 2022 13:47:41 -0000 Date: Sat, 10 Dec 2022 13:47:40 +0000 From: Gianni Ceccarelli To: pgsql-general@lists.postgresql.org Subject: Re: Regular expression to UPPER() a lower case string Message-ID: <20221210134740.2dbf966e@nautilus> In-Reply-To: References: <5C_J_wdWjnwxieBfiMQgeIXLQ5RYXN4-W5jIZyCpkAqlhGO8GxRqwb1jCHfh0EO9SvHYcysvVUXM-ocEBPSb9A-_iiSwfY0DKvljrZ50iA4=@protonmail.com> <20221210104141.35498bc4@thenautilus.net> <20221210120654.736b59eb@nautilus> X-Mailer: Claws Mail 4.1.1git6 (GTK 3.24.34; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2022-12-10 Eagna wrote: > Hi again, and thanks for sticking with this. > > You haven't explained what you're trying to accomplish. > > Ok. > > CREATE TABLE test(x TEXT); > > INSERT INTO test VALUES ('abc'); > > SELECT REGEXP_REPLACE(x, '', '', 'g') > FROM test; > > Expected result: ABC `select upper(x) from test` You haven't explained why you need regexes at all. If you want to convert the value of a text column to uppercase, there's a function for that. If you want to do something else, please describe the actual thing you want to do. Not "how", but "what". > David Johnston suggested something along these lines: > > ========== > > 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. > =========== > > But it's a bit above my pay grade to do this - I've tried, but no go! > :-( It *_appears_* to me that the string's length would have to be > hard coded under this strategy - but if that's the only way, then so > be it. Maybe you need to look at `regexp_split_to_array` or `regexp_split_to_table`? -- Dakkar - GPG public key fingerprint = A071 E618 DD2C 5901 9574 6FE2 40EA 9883 7519 3F88 key id = 0x75193F88