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.

Syntax

TRANSLATE ( char_expression , from_set , to_set )

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 thenth 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.

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

Compatibility

Progress extension