escaping single quotes using regexp_replace

Question

I need to replace any special characters with a space from an input column. Also, additionally, need to suppress any single quotes as well.

I tried this that worked in replacing the special characters, but, it wouldn't suppress the single quotes however.

Input string from a database column: 'SEE'S CANDY?SHOPS INCORPORATED'

If I dont consider single quotes, then \W works to satisfy the first condition.

select regexp_replace('SEES CANDY?SHOPS INCORPORATED', '\W', ' ') from dual;

Output: SEES CANDY SHOPS INCORPORATED

However, outout required:

SEES CANDY SHOPS INCORPORATED while considering single quotes by escaping/suppressing it.

Answer 1

select regexp_replace('SEES''CANDY?SHOPS_INCORPORATED123', '[^[:alnum:]''_]', ' ') from dual;

[:alnum:] Alphanumeric characters. Includes letters and numbers.

\W A nonword character (not a letter, not a number, not an underscore).

Remove single quotes then replace \W with a space:

select regexp_replace(replace(s, ''''), '\W', ' ') 
from (select q'[SEE'S candy?shops_incorporated123]' s from dual);

q'[]' is just an Oracle quote operator (lets you use single quotes inside the brackets without doubling them in order to escape)