Alphabet with numbers.

In Oracle, use the TRANSLATE() function to remove numeric characters from a string using the SQL statement.

Translate() Syntax

TRANSLATE(string, from_str, to_str);

TRANSLATE() function returns a string with all occurrences of each character in from_str replaced by the corresponding character in to_str.

If from_str is longer than to_str, any extra characters in from_str not in to_str are removed from the string, since they have no corresponding characters. to_str cannot be empty.

Remove Numeric Characters from a String Example

The following is an example of an Oracle SQL statement to remove the numeric characters from a string "A1 quick0 br9own fox1 jumps2 righ1t o4ver a laz5y d7og.".

In the below SQL statement I am using the REPLACE() function with the TRANSLATE() function, because we cannot use a NULL string for to_str, so I am translating all the numeric characters with "1", then replacing it with a NULL value using the REPLACE() function.

select replace(
    'A1 quick0 br9own fox1 jumps2 righ1t o4ver a laz5y d7og.',         '0123456789', '1')
, 1, '') num_removed from dual;


A quick brown fox jumps right over a lazy dog.

See also:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of and a question and answer forum for developers.

Leave a Reply