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 Russian character set. The translation must already exist, either by default or having been created using the CREATE TRANSLATION command.

SQL99 Syntax

CONVERT (char_value target_char_set USING form_of_use source_char_name)

TRANSLATE(char_value target_char_set USING translation_name)

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

MySQL’s implementation of the CONVERT function only translates numbers from one base to another. In contrast, Microsoft 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 SQL99 CONVERT function. PostgreSQL does not support CONVERT, and its implementation of TRANSLATE serves to morph any occurrence of a character string to any other character string.

Get SQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.