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.
WHEN '000' THEN 'NUMERIC '
WHEN ' ' THEN 'ALPHANUMERIC'
) AS TYPE
FROM YDGF450P.DEPARTMENT D
) AS T ;
The results of this query:
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.
SELECT TRANSLATE('THIS$IS$A$TEST.', ‘ ‘, '$' )
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.