Removing un-wanted text from strings in Oracle

Performing string manipulation is very common regardless of the RDBMS that we use. There are lots of string functions available like substr, instr, lpad, rpad etc. to perform string manipulation. We have also covered some of them in our previous blog posts. Recently at the client site, we came across the requirement where we have to remove the ‘-‘ from the phone number string before writing the record to flat file.  There are different ways of removing unwanted characters from the string using:

•    REPLACE function
•    TRANSLATE function
•    REGEXP_REPLACE function (10g and above)

We can use REPLACE function to remove the unwanted character from the string. It takes three arguments.

•    String from which we want to remove or replace the character.
•    Search string.
•    Replace string.

SQL> SELECT REPLACE(‘1-770-123-5478′,’-‘,”) COL1 FROM DUAL;

OR

SQL> SELECT REPLACE(‘1-770-123-5478′,’-‘,NULL) COL1 FROM DUAL;

COL1
—————
17701235478

Another way to do this is to use TRANSLATE function in such a way so that we can get rid of unwanted characters. It takes three arguments.
•    String from which we want to remove or replace the character.
•    Character which we would like to replace or remove from the string in the first argument
•    New character with which we want to replace the old character (2nd argument).

Let us see it with example. We will first try to replace ‘-‘ with ‘*’ in the string using translate.

SQL>  SELECT TRANSLATE(‘1-770-123-4567′,’-‘,’*’) COL1 FROM DUAL;

COL1
————–
1*770*123*4567

In above example, we replaced character ‘-‘ with ‘*’ but we were talking about removing ‘-‘ from the string. Let us execute following query.

SQL> SELECT TRANSLATE(‘1-770-123-4567’,’@-‘,’@’) COL1 FROM DUAL;

COL1
—————
17701234567

Above result indicates that we are able to remove unwanted character ‘-‘ from the string.  In order to understand this, we have to understand how translate works. TRANSLATE function, takes each character from the ‘from string(2nd argument) and replace it with the corresponding character from the ‘to string(3rd argument). In our example, ‘@’ will be replaced with the ‘@’ and ‘-‘ will be replaced with null value since we are not providing any corresponding character for it in the ‘to string’. This will become more clear with the following example.

SQL> SELECT TRANSLATE(‘DECIPHER’,’DE’,’12’) COL1 FROM DUAL;

COL1
—————
12CIPH2R

In above example, occurrence of ‘D’ is replaced with 1 and each occurrence of ‘E’ is replaced with 2.

Somebody might think that why can’t we, use NULL as a replacement character in the 3rd argument. Let us see what result we get when we execute following query.

SQL> SELECT TRANSLATE(‘1-770-123-4567′,’-‘,NULL) COL1 FROM DUAL;

COL1
———-

This is because when we pass NULL argument to TRANSLATE function, it returns null and hence we don’t get the desired result.

Other thing to note about TRANSLATE function is that it is case-sensitive. So if there is a case mismatch, translation will not take place. In following example, we are trying to replace ‘d’  with 1 but ‘d’ does not exist in the string and hence it will not be replaced. Only ‘E’ will be replaced with 2. Query and result is as shown below.

SQL> SELECT TRANSLATE(‘DECIPHER’,’dE’,’12’) COL1 FROM DUAL;

COL1
—————
D2CIPH2R

The way TRANSLATE function differs from REPLACE is that, TRANSLATE function provides single character one to one substitution while REPLACE allows you to replace one string with another.

Starting 10g, Oracle introduced regular expression function REGEXP_REPLACE. We can strip unwanted character from the string using this function as well.

SQL> SELECT REGEXP_REPLACE(‘1-770-123-5478′,’-‘,NULL) COL1 FROM DUAL;

COL1
———–
17701235478