From: | Denisa Cirstescu <Denisa(dot)Cirstescu(at)tangoe(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Regex Replace with 2 conditions |
Date: | 2018-02-05 13:34:37 |
Message-ID: | CY1PR12MB0025991A94F3519B1CB8796BE6FE0@CY1PR12MB0025.namprd12.prod.outlook.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
Is there a way to specify 2 conditions in regexp_replace?
I need an SQL function that eliminates all ASCII characters from 1-255 that are not A-Z, a-z, 0-9, and special characters % and _ so something like:
SELECT regexp_replace(p_string, E'[' || CHR(1) || '-' || CHR(255) || '&&[^A-Za-z0-9%_]]', '', 'g'));
But this syntax is not really working.
I have written a SQL function that achieves this, but I am not happy with it because it is hard to read and maintain:
-- Eliminates all ASCII characters from 1-255 that are not A-z, a-z, 0-9, and special characters % and _
-- The computed regex expression that is between E[] is CHR(1)-$&-/:-(at)[-^`{-ÿ](dot)
CREATE OR REPLACE FUNCTION testFunction(p_string CHARACTER VARYING) RETURNS VARCHAR AS $$
SELECT regexp_replace(p_string, E'[' || CHR(1) || '-' || CHR(36) || CHR(38) || '-' || CHR(47) || CHR(58) || '-' || CHR(64) || CHR(91) || '-' || CHR(94) || CHR(96) || CHR(123) || '-' || CHR(255) || ']', '', 'g');
$$ LANGUAGE sql IMMUTABLE;
Please help me figure out how to achieve this.
Thanks a lot,
Denisa Cîrstescu
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2018-02-05 14:26:46 | Re: Regex Replace with 2 conditions |
Previous Message | Thomas Poty | 2018-02-05 13:09:14 | Re: weird result by changing type enum array to text array of a column |