Oracle Query - Get only strings in the select field

Question

Hi all!

Maybe this sounds a little bit crazy, but I need to come up with a query to retrieve only letters out of an alphanumeric field.

For example:

TABLE
1234ADD
3901AC
1812OPA
82711AUU

RESULTS EXPECTED
ADD
AC
OPA
AUU

Thank you!

Answer 1

SELECT  REGEXP_REPLACE('1234ADD 3901AC 1812OPA 82711AUU', '[0-9]', '')
FROM    dual

Answer 2

Looks like you only want to remove numbers. You can use REGEXP_REPLACE for that in 10g or 11g:

SELECT REGEXP_REPLACE( your_column, '[0-9]*', '' ) FROM your_table;

Answer 3

Try

SELECT TRANSLATE('1234ADD 3901AC 1812OPA 82711AUU', 'A1234567890', 'A') FROM dual;

and in general see: http://www.psoug.org/reference/translate_replace.html