Chapter 4: SQL Language Elements
4.9.2.94 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.