Home » SQL & PL/SQL » SQL & PL/SQL » Extract only Alphabets.
Extract only Alphabets. [message #191175] Tue, 05 September 2006 02:02 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi,

This is my scenario,

Need to extract only alphabets from a given string,whatever it may contain for example (Numbers,special characters,spaces etc.).

Regards,
Marlon
Re: Extract only Alphabets. [message #191209 is a reply to message #191175] Tue, 05 September 2006 03:44 Go to previous messageGo to next message
A Ikramur Rahman
Messages: 81
Registered: May 2004
Member
You can make use of TRANSLATE function to achieve this. For example,

select TRANSLATE( 'this*55573command$53274will44226print23224only1234alphabets123456789!$%^&*()_+),',
'0123456789!$%^&*()_+),',
' '
)
from dual ;

Re: Extract only Alphabets. [message #191213 is a reply to message #191209] Tue, 05 September 2006 03:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
link

Thanks to Art Metzer.

MHE
Re: Extract only Alphabets. [message #191349 is a reply to message #191175] Tue, 05 September 2006 16:40 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Couple other options:

SQL> set def off
SQL> select replace(translate('te#*xt$1on*&123)(!l@y',
  2  	translate(upper('te#*xt$1on*&123)(!l@y'),
  3 	'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),' '),' ') "Only ALPHA" from dual;

Only ALPHA
-----------
textonly


And luckily, 10g makes it easier:

SQL> select regexp_replace('te#*xt$1on*&123)(!l@y','[^[:alpha:]]') "Only ALPHA" from dual;

Only ALPHA
-----------
textonly

[Updated on: Tue, 05 September 2006 16:41]

Report message to a moderator

Previous Topic: Variable "in list" using PL/SQL Table
Next Topic: Problem with view
Goto Forum:
  


Current Time: Sat Dec 10 09:16:54 CST 2016

Total time taken to generate the page: 0.11389 seconds