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.
