TRANSLATE STRING clause

Use the TRANSLATE STRING clause to specify characters replacements in the selected string columns of an SQL query.

This function is based on the SQL “TRANSLATE” scalar function. It allows to perform characters replacements based on the TRANSLATE() scalar function on string columns being part of an SQL query, by specifying one or more characters replacements rules which would apply to this SQL query. The function is compatible with string columns only. It simplifies the use of the SQL TRANSLATE() scalar function, without having to specify this function explicitly into the SQL query. For example, in the case of a large number of string columns, instead of specifying the TRANSLATE keyword for each string column, it is possible to directly apply the TRANSLATE() scalar function to all the string columns by using the TRANSLATE STRING clause.
The TRANSLATE STRING clause can be specified at the GLOBAL, UNLOAD and SELECT blocks level.
The following diagram shows the syntax for the TRANSLATE STRING clause.
Figure 1. translate_string_clause

1  TRANSLATE STRING
2.1  ( + ,
2.2.1 UPPER
2.2.1 
2.2.2.1 column_name
2.2.2.1 'literal'
2.2.1 BY
2.2.2.1 column_name
2.2.2.1 'literal'
2.2.1?  PAD WITH
2.2.2.1 column_name
2.2.2.1 'char'
2.1? 
2.2.1 FOR
2.2.1 EXCEPT
2.1 (
2.1 + , column_name
2.1 ) )
2.1  IN ”filename”