On Mon, Feb 5, 2018 at 6:34 AM, Denisa Cirstescu <
denisa.cirste...@tangoe.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)-$&-/:-@[-^`{-ÿ].
>
> 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.
