CODEWITHSUNDEEP
sqloracle-databasestringnumeric
danboh
danboh

Reputation: 778

Oracle Query - Get only strings in the select field

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!

Upvotes: 2

Views: 4434

Answers (3)

p.marino
p.marino

Reputation: 6252

Try

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

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

Upvotes: 2

Peter Lang
Peter Lang

Reputation: 55594

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;

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425803

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

Upvotes: 2

Related Questions