CODEWITHSUNDEEP
regexoracle-databaseplsql
Daniel Paczuski Bak
Daniel Paczuski Bak

Reputation: 4080

How do I remove all characters that aren't alphabetic from a string in PL/SQL?

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.

Upvotes: 4

Views: 8657

Answers (3)

Arunesh Singh
Arunesh Singh

Reputation: 3535

Try this:

SELECT REGEXP_REPLACE('AB$%c','[^a-zA-Z]', '') FROM DUAL;

Or

SELECT REGEXP_REPLACE( your_column, '[^a-zA-Z]', '' ) FROM your_table;

Read here for more information

Upvotes: 0

umut
umut

Reputation: 82

you can use regexp like that:

SELECT REGEXP_REPLACE(UPPER('xYztu-123-hello'), '[^A-Z]+', '') FROM DUAL;

also answered here for non-numeric chars

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

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

Upvotes: 4

Related Questions