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