SQL Query to remove Non-Numeric characters from a String

It’s one of the very common requirement to remove non-numeric values from a text field and we can achieve this in several ways using some functions. In this article I have listed the ways I am aware of.

Using TRANSLATE and REPLACE Function

We can use this method when we are completely aware of what all non-numeric characters that would be present in the input value. Here in this example i am trying to remove non-numeric characters from phone number field

SELECT TRANSLATE(REPLACE(LOWER('Ph +91 984-809-8540'),'(0) -',' '),'abcdefghijklmnopqrstuvwxyz()- +/,.#',' ') OUT_PUT FROM dual;

The above query would return a output as “919848098540” but if the input is something like ‘Ph: +91 984-809-8540‘ then this would return an output ‘:919848098540‘ since we are not handling the character ‘:‘. So we can’t go for this method if we are not sure of the all possible non-numeric characters that would come in as input.

Using Regular Expression:

regexp_replace function replaces string with regular expression matching supports. The simplest format for this function is:

REGEXP_REPLACE (source_string, pattern_to_find, pattern_to_replace_by)

For more information about regexp_replace please read this article from oracle.com

SELECT to_number(regexp_replace('Ph: +91 984-809-8540', '[^0-9]+', '')) OUT_PUT FROM dual;

The above statement would replace all the characters except the digits 0-9 with null.

SELECT to_number(regexp_replace('Ph: +91 984-809-8540', '\D', '')) OUT_PUT FROM dual;

In this statement ‘\D’ would find all Non-digit characters and the will be replaced by null.

Though the above two statements works well, there is a scenario where these two statements fail to work, let me tell you with an example

SELECT to_number(regexp_replace('0*0-7-', '[^0-9]+', '')) OUT_PUT FROM dual;
SELECT to_number(regexp_replace('0*0-7-', '\D', '')) OUT_PUT FROM dual;

we expect the above statement to return ‘007’ instead they would return ‘7’. this method omitts if we have digit ‘0’ as prefix.

The best method I found is to use regexp Metacharacter ‘:digit:‘ which matches digits 0-9

SELECT REGEXP_REPLACE( '0*0-7-', '[^[:digit:]]', NULL ) OUT_PUT FROM DUAL;

SELECT regexp_replace( 'Ph: +91 984-809-8540', '[^[:digit:]]', NULL ) FROM dual;

To know more on Regexp_Replace Metacharacter, read this article from Jav2s.com