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.