Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

TRANSLATE function (extension)

Syntax

TRANSLATE ( char_expression, from_set, to_set )

Description

The scalar function TRANSLATE translates each character in char_expression that is in from_set to the corresponding character in to_set. The translated character string is returned as the result. This function is similar to the Oracle TRANSLATE function.

Example

This example substitutes underscores for spaces in customer names.

SELECT TRANSLATE (customer_name, ' ', '_')

"TRANSLATE Example" from customers;

TRANSLATE EXAMPLE

-----------------

Sports_Cars_Inc.__________________________________

Mighty_Bulldozer_Inc._____________________________

Ship_Shapers_Inc._________________________________

Tower_Construction_Inc.___________________________

Chemical_Construction_Inc.________________________

Aerospace_Enterprises_Inc.________________________

Medical_Enterprises_Inc.__________________________

Rail_Builders_Inc.________________________________

Luxury_Cars_Inc.__________________________________

Office_Furniture_Inc._____________________________

10 records selected

Notes

  • char_expression, from_set, and to_set can be any character expression.
  • For each character in char_expression, TRANSLATE checks for the same character in from_set:
  • If it is in from_set, TRANSLATE translates it to the corresponding character in to_set (if the character is the nth character in from_set, the nth character in to_set).
  • If the character is not in from_set TRANSLATE does not change it.
  • If from_set is longer than to_set, TRANSLATE does not change trailing characters in from_set that do not have a corresponding character in to_set.
  • If either from_set or to_set is null, TRANSLATE does nothing.

TOCIndex