Alphabet with numbers.

Oracle SQL - Remove Numeric Characters from a String

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

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(
    translate(
    'A1 quick0 br9own fox1 jumps2 righ1t o4ver a laz5y d7og.',         '0123456789', '1')
, 1, '') num_removed from dual;

Output

A quick brown fox jumps right over a lazy dog.

See also: