Oracle Builtin String Character Classes

Question

Does Oracle have built-in string character class constants (digits, letters, alphanum, upper, lower, etc)?

My actual goal is to efficiently return only the digits [0-9] from an existing string.

Unfortunately, we still use Oracle 9, so regular expressions are not an option here.

Examples The field should contain zero to three letters, 3 or 4 digits, then zero to two letters. I want to extract the digits.

String    --> Result
ABC1234YY --> 1234
D456YD    --> 456
455PN     --> 455

Answer 1

Update: Doh! misread the question, this applies to Oracle 10 and later.

Original: Yes, see regular expressions.

For example, here's a test for non-digits in a zip code field:

SELECT zip
  FROM zipcode
 WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP
-----
ab123
123xy
007ab
abcxy

Answer 2

You can check the list for predefined datatypes on Oracle here, but you are not going to find what are you looking for.

To extract the numbers of an string you can use some combination of these functions:

• TO_NUMBER, to convert an string to number.
• REPLACE, to remove occurences.
• TRANSLATE, to convert chars.

If you provide a more concise example will be easier to give you a detailed solution.

Answer 3

No string constants, but you can do:

select translate
      ( mystring
      , '0'||translate (mystring, 'x0123456789', 'x')
      , '0'
      )
from mytable;

For example:

select translate
      ( mystring
      , '0'||translate (mystring, 'x0123456789', 'x')
      , '0'
      )
from
( select 'fdkhsd1237ehjsdf7623A@L:P' as mystring from dual);

TRANSLAT
--------
12377623

If you want to do this often you can wrap it up as a function:

create function only_digits (mystring varchar2) return varchar2
is
begin
   return
      translate
         ( mystring
         , '0'||translate (mystring, 'x0123456789', 'x')
         , '0'
         );
end;
/

Then:

SQL> select only_digits ('fdkhsd1237ehjsdf7623A@L:P') from dual;

ONLY_DIGITS('FDKHSD1237EHJSDF7623A@L:P')
-----------------------------------------------------------------
12377623

Answer 4

If you are able to use PL/SQL here, another approach is write your own regular expression matcher function. One starting point is Rob Pike's elegant, very tiny regular expression matcher in Chapter 1 of Beautiful Code. One of the exercises for the reader is to add character classes. (You'd first need to translate his 30 lines of C code into PL/SQL.)