Re: [Q] Removing all characters except some

use the TRANSLATE function with TO_NUMBER

the trick is to begin the 2nd arg with a character that you want to preserve, followed by all characters you want to 'zap', and in the 3rd arg just include the single character that should be preserved -- all other listed characters are translated to nulls, and any unlisted characters are left unchanged (except, of course, for their position in the output string

  select to_number(translate(lower(columnName), '0abcdefg...', '0')

wrapping the column lower() simply shortens the length of the 2nd arg by 26 characters

you might want to encapsulate the translate expression in a stored function since the expression is long and is likely to be reused in multiple queries

  create or replace function extract_digits( ip_value in varchar2 )   return number
  is

      return to_number(translate(lower(ip_value),'0abcde ....   end;

don't forget to include punctuation characters and the space in the 2nd arg

downside: not NLS compatible

-- 
Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting

"Peter Amberg" <son42uk_at_yahoo.co.uk> 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.

|

| Any help greatly appreciated!

|

| Peter