Search code examples
sqlregexoracle-databaseoracle11goracle9i

Extract only alphabetic characters from VARCHAR column

I have a column of type VARCHAR2 which has text like '12-MAR-2014,,1234,DATA,VALUE,12/03/14,,124,End'. And I need the output to be, '%DATA%VALUE%End%'. i.e. the text to be delimited by comma ','. Only alphabets need to be selected leaving out the date or numeric or special characters. Something like,

select some_function('12-MAR-2014,,1234,DATA,VALUE,12/03/14,,124,End') as output from dual;

OUTPUT
---------
%DATA%VALUE%End%

EDIT:

Another added requirement where alphanumeric values need to be retained:

select some_function('12-MAR-2014,,1234,DATA65,VALUE,12/03/14,,124,End') as output from dual;

OUTPUT
---------
%DATA65%VALUE%End%
Solution

  • Something like this may help you:

    select regexp_replace('12-MAR-2014,,1234,DATA,VALUE,12/03/14,,124,End','[[0-9]{2}-(JAN|FEB|MAR|APR|JUN|JUL|AUG|SEP|OCT|NOV|DEC)-[0-9]{4}]*|[^a-zA-Z]+|$','%') from dual