SQL SERVER : 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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table