Re: [Q] Removing all characters except some

son42uk_at_yahoo.co.uk (Peter Amberg) wrote in message news:<f8232d10.0312030449.38c691aa_at_posting.google.com>...
> I have a VARCHAR column in some table T. How can I zap away all
> characters from the string except digits? I need this in a SELECT
> clause, I need to order the entries by the integer values within the
> string. There may be non-digit characters anywhere in the string, I
> can't just use SUBSTR.
>

You can also use two translates in cases where you only want to specify the characters to keep.

SQL> var s varchar2(100)
SQL> exec :s := 'rubbish123in456here7_at_8*9!!'

PL/SQL procedure successfully completed.

SQL> select
  2 translate(:s,'0'||translate(:s,'*0123456789','*'),'0')   3 from dual
  4 /

TRANSLATE(:S,'0'||TRANSLATE(:S,'*0123456789','*'),'0')

123456789

SQL> exec :s := ' 1 xx 2 3 p 1'

PL/SQL procedure successfully completed.

SQL> / TRANSLATE(:S,'0'||TRANSLATE(:S,'*0123456789','*'),'0')

1231

-- 
Martin Burbridge

pobox 002 email is full of spam and never read