Recipe6.4.Removing Unwanted Characters from a String

Recipe 6.4. Removing Unwanted Characters from a String

Problem

You want to remove specific characters from your data. Consider this result set:

 ENAME             SAL ---------- ---------- SMITH             800 ALLEN            1600 WARD             1250 JONES            2975 MARTIN           1250 BLAKE            2850 CLARK            2450 SCOTT            3000 KING             5000 TURNER           1500 ADAMS            1100 JAMES             950 FORD             3000 MILLER           1300 

You want to remove all zeros and vowels as shown by the following values in columns STRIPPED1 and STRIPPED2:

 ENAME      STRIPPED1         SAL STRIPPED2 ---------- ---------- ---------- --------- SMITH      SMTH              800 8 ALLEN      LLN              1600 16 WARD       WRD              1250 125 JONES      JNS              2975 2975 MARTIN     MRTN             1250 125 BLAKE      BLK              2850 285 CLARK      CLRK             2450 245 SCOTT      SCTT             3000 3 KING       KNG              5000 5 TURNER     TRNR             1500 15 ADAMS      DMS              1100 11 JAMES      JMS               950 95 FORD       FRD              3000 3 MILLER     MLLR             1300 13 

Solution

Each DBMS provides functions for removing unwanted characters from a string. The functions REPLACE and TRANSLATE are most useful for this problem.

DB2

Use the built-in functions TRANSLATE and REPLACE to remove unwanted characters and strings:

 1 select ename, 2        replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1, 3        sal, 4        replace(cast(sal as char(4)),'0','') stripped2 5   from emp 

MySQL and SQL Server

MySQL and SQL Server do not offer a TRANSLATE function, so several calls to REPLACE are needed:

  1 select ename,  2        replace(  3        replace(  4        replace(  5        replace(  6        replace(ename,'A',''),'E',''),'I',''),'O',''),'U','')  7        as stripped1,  8        sal,  9        replace(sal,0,'') stripped2 10   from emp 

Oracle and PostgreSQL

Use the built-in functions TRANSLATE and REPLACE to remove unwanted characters and strings:

 1 select ename, 2        replace(translate(ename,'AEIOU','aaaaa'),'a') 3        as stripped1, 4        sal, 5        replace(sal,0,'') as stripped2 6   from emp 

Discussion

The built-in function REPLACE removes all occurrences of zeros. To remove the vowels, use TRANSLATE to convert all vowels into one specific character (I used "a"; you can use any character), then use REPLACE to remove all occurrences of that character.