SQL in a Nutshell, 2nd Edition

Name

CONVERT and TRANSLATE

Synopsis

The CONVERT function alters the representation of a character string within its character set and collation. For example, CONVERT might be used to alter the number of bits per character.

TRANSLATE alters the character set of a string value from one base-character set to another. Thus, TRANSLATE might be used to translate a value from the English character set to a Kanji (Japanese) or Cyrillic (Russian) character set. The translation must already exist, either by default or by having been created using the CREATE TRANSLATION command.

SQL2003 Syntax

CONVERT (char_value USING conversion_char_name)

TRANSLATE(char_value USING translation_name)

CONVERT converts char_value to the characterset with the name supplied in conversion_char_name. TRANSLATE converts char_value to the character set provided in translation_name.

Among the platforms, only Oracle supports CONVERT and TRANSLATE with the same meaning as SQL2003. Oracle’s implementation of TRANSLATE is very similar to SQL2003, but not identical. In its implementation, Oracle accepts only two arguments and translates only between the database character set and the national language support character set.

MySQL’s implementation of the CONV function only translates numbers from one base to another. In contrast, SQL Server’s implementation of CONVERT is a very rich utility that alters the base datatype of an expression, but is otherwise dissimilar to the SQL2003 CONVERT function. PostgreSQL does not support ...