Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from SQL in a Nutshell

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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443