Re: Regex Replace with 2 conditions

On Mon, Feb 5, 2018 at 6:34 AM, Denisa Cirstescu <
Denisa(dot)Cirstescu(at)tangoe(dot)com> wrote:

> Is there a way to specify 2 conditions in regexp_replace?
>

​Tom and Francisco ​both give excellent responses.

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;
>

​​I'm not seeing what kind of maintenance would be involved here - and you
have various string tricks to use to make the expression itself more
comprehensible (at the possible cost of performance).

control_codes_1 := CHR(1) || '-' || CHR(36)
control_codes_2 := CHR(38) || '-' || CHR(47)

regexp_replace(
p_string,
format('[%s%s%s%s%s%s]',
control_codes_1,
control_codes_2,
blah1,
blah2,
blah3,
blah4
),
'x')

Add a code comment and the next person to read this should be able to
understand its purpose.

Note, as a matter of course I try to avoid E'' strings whenever I write
regular expressions - since backslash is special to both I have to escape
the ones being passed to the regex engine and that is undesirable.

David J.