selecting text only

[rgaskell]

I have a row of data i.e ////123/hello/12/2/3/34343/2/2/2/2

How do I select just the text back. The start position can be different aswell for different rows and also the length of the text can be different... I believe on 10g you can use REGEXP_ functions but what about 9i?

thanks

[ebrian]

SQL> select replace(translate('////123/hello/12/2/3/34343/2/2/2/2',
  2  	translate(upper('////123/hello/12/2/3/34343/2/2/2/2'),
  3 	'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),' '),' ') "Only ALPHA" from dual;

ONLY ALPHA
----------
hello

[LKBrwn_DBA]

Or just try this:

SQL> select translate('////123/hello/12/2/3/34343/2/2/2/2',
  2* ' /0123456789',' ') "Only ALPHA" from dual;
Only
-----
hello 

[ebrian]

Same thing I was thinking LKBrwn_DBA. However, I wasn't sure if the OP was going to have different characters present in his data, so I just accommodated for ANYTHING but ALPHA. If you don't expect anything non-alpha besides "/" or numbers, then definitely use LKBrwn_DBA's method.

[marist89]

is it always after the 5th / but before the 6th?