How can I remove all characters that are not alphabetic from a PL / SQL string?

advertisements

I have a PL/SQL procedure and I need to take a string and remove all characters that aren't alphabetic. I've seen some examples and read documentation about the REGEXP_REPLACE function but can't understand how it functions.

This is not a duplicate because I need to remove punctuation, not numbers.


Either:

select regexp_replace('1A23B$%C_z1123d', '[^A-Za-z]') from dual;

or:

select regexp_replace('1A23B$%C_z1123d', '[^[:alpha:]]') from dual;

The second one takes into account possible other letters like:

select regexp_replace('123żźć', '[^[:alpha:]]') from dual;

Result:

żźć

Also to answer your question about how the functions works: the first parameter is the source string, the second - a regular expression - everything which will be matched to it, will be replaced by the third argument (optional, NULL by default, meaning all matched characters will just be removed).

Read more about regular expressions:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm