Replacing multiple special characters in oracle

Question

I have a requirement in oracle to replace the special characters at first and last position of the column data.

Requirement: only [][.,$'*&!%^{}-?] and alphanumberic characters are allowed to stay in the address data and rest of the characters has to be replaced with space.I have tried in below way in different probabilities but its not working as expected. Please help me in resolving this.

SELECT emp_address,
       REGEXP_REPLACE(
         emp_address,
         '^[^[[][.,$'\*&!%^{}-?\]]]|[^[[][.,$'\*&!%^{}-?\]]]$'
       ) AS simplified_emp_address
FROM   table_name

Answer 1

As per the regular expression operators and metasymbols documentation:

• Put ] as the first character of the (negated) character group;
• - as the last; and
• Do not put . immediately after [ or it can be matched as the start of a coalition element [..] if there is a second . later in the expression.

Also:

• Double up the single quote (to escape it, so it does not terminate the string literal); and
• Include the non-special characters a-zA-Z0-9 in the capture group too otherwise they will be matched.

Which gives you the regular expression:

SELECT emp_address,
       REGEXP_REPLACE(
         emp_address,
         '^[^][,.$''\*&!%^{}?a-zA-Z0-9-]|[^][,.$''\*&!%^{}?a-zA-Z0-9-]$'
       ) AS simplified_emp_address
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (emp_address) AS
SELECT '"test1"' FROM DUAL UNION ALL
SELECT '$test2$' FROM DUAL UNION ALL
SELECT '[test3]' FROM DUAL UNION ALL
SELECT 'test4' FROM DUAL UNION ALL
SELECT '|test5|' FROM DUAL;

Outputs:

EMP_ADDRESS	SIMPLIFIED_EMP_ADDRESS
"test1"	test1
$test2$	$test2$
[test3]	[test3]
test4	test4
|test5|	test5

db<>fiddle here