Oracle: Special characters filter with few exceptions

Question

I need some quick help.

I want to filter the input string and remove special characters except space( ), period(.), comma(,), hyphen(-), ampersand(&) and apostrophe(').

I am using below but it's filtering out everything except period(.) and comma(,).

SELECT REGEXP_REPLACE('*Bruce*-*Martha*-&-*Thomas%* *Wyane''s* *Enterprises* ([#Pvt,Ltd.])', '[^0-9A-Za-z,.'' ]', '')  
FROM dual;

Input String: *Bruce*-*Martha*-&-*Thomas%* *Wyane's* *Enterprises* ([#Pvt,Ltd.])

What I am expecting: Bruce-Martha-&-Thomas Wyane's Enterprises Pvt,Ltd.

What I am getting: BruceMarthaThomas Wyane's Enterprises Pvt,Ltd.

Thanks.

Answer 1

You may use

SELECT REGEXP_REPLACE('*Bruce*-*Martha*-&-*Thomas%* *Wyane''s* *Enterprises* ([#Pvt,Ltd.])', '[^&0-9A-Za-z,.'' -]+', '') FROM dual

See the regex demo

The [^&0-9A-Za-z,.'' -]+ pattern will match one or more occurrences of any char but &, ASCII letter, digit, comma, dot, single apostrophe, space and hyphen.

To support any whitespace, replace the literal space with [:space:]:

'[^&0-9A-Za-z,.''[:space:]-]+'