15.00 - OTRANSLATE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)
Last Update



Returns source_string with every occurrence of each character in from_string replaced with the corresponding character in to_string.




Syntax element…



the name of the database where the function is located.


a character string or string expression.

If source_string is NULL, the function returns NULL.


a string of characters that will be replaced in source_string.

If from_string is NULL, the function returns source_string.


a string of characters that replaces the characters specified by from_string.

If to_string is:

  • NULL is NULL the function returns NULL.
  • An empty string, the corresponding character in from_string is removed.
  • ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.


    OTRANSLATE is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

    Argument Types and Rules

    Expressions passed to this function must have one of the following data types: CHAR or VARCHAR

    You can also pass arguments with data types that can be converted to the above types using the implicit data type conversion rules that apply to UDFs.

    Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.

    For details, see “Compatible Types” in SQL External Routine Programming.

    Result Type

    The result data type is VARCHAR. The character set is the same as that of the source_string argument.

    Usage Notes  

    If the first character in from_string occurs in the source_string, all occurrences of it are replaced by the first character in to_string. This repeats for all characters in from_string and for all characters in from_string. The replacement is performed character-by-character, that is, the replacement of the second character is done on the string resulting from the replacement of the first character.

    If from_string contains more characters than to_string, the extra characters are removed from the source_string. If an extra character was involved in a mapping, it is not removed, just ignored.

    If from_string contains fewer characters than to_string, the extra characters in to_string have no effect.

    If the same character occurs more than once in from_string, only the replacement character from the to_string corresponding to the first occurrence is used.


    The following query returns the string 'TD14.0 is the current database version'. The occurrence in source_string of the character in from_string ('3') is replaced by the character in to_string ('4').

       SELECT OTRANSLATE('TD13.0 is the current database version',
          '3', '4');


    In the following query, the characters 'T' and 'h' are replaced with 'S' and 'p', resulting in the string 'Spin and Spick'. Next, the extra character 'k' in the from_string (where there is no corresponding character in the to_string) is removed from the source_string. The resulting string is 'Spin and Spic'.

       SELECT OTRANSLATE('Thin and Thick', 'Thk', 'Sp');


    In the following query, the characters 'T' and 'h' are replaced with 'S' and 'p'. The character 'T' occurs twice in the from_string, but only the replacement character in the to_string that corresponds to the first occurrence of 'T' is used. That is, only 'S' is used to replace 'T'. Next, the character 'k' is replaced with 'x', and the extra characters 'y' and 'z' in the to_string are ignored. The resulting string is 'Spin and Spicx'.

       SELECT OTRANSLATE('Thin and Thick', 'ThTk', 'Sptxyz');


    The following query returns the source_string unchanged since from_string is NULL.

       SELECT OTRANSLATE('Thin and Thick', NULL, 'Spt');