May 12, 2009

Using the Translate Function in SQL

The translate function can be a very powerful tool in the SQL coder's tool belt. TRANSLATE converts a string expression character by character using a "from string," or translation table, with a matching character in the "to string."

I actually used to wonder why I'd ever need to use this function. Soon enough I realized that sometimes you may need to know if a string column like CHAR or VARCHAR contains all numeric or all alphanumeric values.

What follows is a demonstration of how TRANSLATION works. I used the IBM sample table (DSN8810.DEPT), which has a department number column (DEPTNO) defined as CHAR(03). The sample data has mixed alphanumeric and numeric values. I added department 123 and XYZ to the table just for demonstration purposes.

In the sample SQL below, TRANSLATE converts each character found in the DEPTNO column. The third parameter is the from string. When the character in the from string is found the corresponding to string, the value is displayed in the result string. In this example the character "0 through 9" is converted to an "0" and "A through Z" results in a space character (' '). The result is passed up to the outer select, which applies a case statement to return the type of data being stored in the column.

SELECT T.DEPTNO                                                      
 ,CASE T.TYPE                                                        
    WHEN '000' THEN 'NUMERIC             '                                      
    WHEN '   '    THEN 'ALPHANUMERIC'                                   
    ELSE 'MIXED'                                                     
  END TYPE                                                           
FROM (                                                               
       SELECT D.DEPTNO                                               
                           ,'0000000000                                                                     '   
                   ) AS TYPE                                         
       FROM YDGF450P.DEPARTMENT D                                    
     ) AS T  ;

The results of this query:
A00          MIXED      
B01          MIXED      
C01          MIXED      
D01          MIXED      
D11          MIXED      
D21          MIXED      
E01          MIXED      
E11          MIXED      
E21          MIXED      
123          NUMERIC           

Another reason you may need to use TRANSLATE is to fix a data processing problem. Maybe the string contains a special character (e.g., $) that must be removed so your application can process correctly. Let's assume that "$" is in the string and you need to replace it with spaces (' ').

This use of TRANSLATE would correct this problem.

  FROM SYSIBM.SYSDUMMY1                       

Results:    THIS IS A TEST.

If you have some examples of how TRANSLATE has solved problems for you, please post it in Comments so everyone learn more about this great DB2 function.