Remove Special Characters from an Oracle String

Question

From within an Oracle 11g database, using SQL, I need to remove the following sequence of special characters from a string, i.e.

~!@#$%^&*()_+=\{}[]:”;’<,>./?

If any of these characters exist within a string, except for these two characters, which I DO NOT want removed, i.e.: "|" and "-" then I would like them completely removed.

For example:

From: 'ABC(D E+FGH?/IJK LMN~OP' To: 'ABCD EFGHIJK LMNOP' after removal of special characters.

I have tried this small test which works for this sample, i.e:

select regexp_replace('abc+de)fg','\+|\)') from dual

but is there a better means of using my sequence of special characters above without doing this string pattern of '\+|\)' for every special character using Oracle SQL?

Answer 1

You can replace anything other than letters and space with empty string

[^a-zA-Z ]

here is online demo

---

As per below comments

I still need to keep the following two special characters within my string, i.e. "|" and "-".

Just exclude more

[^a-zA-Z|-]

Note: hyphen - should be in the starting or ending or escaped like \- because it has special meaning in the Character class to define a range.

For more info read about Character Classes or Character Sets