CODEWITHSUNDEEP
sqlregexoracle-databaseoracle11goracle9i
zephyrus
zephyrus

Reputation: 261

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%

Upvotes: 0

Views: 4153

Answers (2)

pablomatico
pablomatico

Reputation: 2242

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You can use regexp_replace(). I think the expression is something like this:

select regexp_replace(col, '([^a-zA-Z]+)', '%')

Unfortunately, I cannot test this right now (and SQL Fiddle is down).

Upvotes: 1

Related Questions