SQL TRANSLATION

What does the SQL TRANSLATION statement do?

Defines a reusable character-set mapping so the database can convert text from one character set to another.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TRANSLATION: IBM Db2: Yes. PostgreSQL: No. MySQL: No. SQL Server: No. Oracle: Partial via NLS conversion functions, not CREATE TRANSLATION. SQLite: No.

SQL TRANSLATION Full Explanation

TRANSLATION is an ANSI/ISO SQL object created with the CREATE TRANSLATION statement. It stores a one-to-one mapping between code points in a source character set and a target character set. When a TRANSLATION exists, the database can automatically or explicitly convert string literals, column values, and parameters between those character sets during CAST, assignment, and comparison operations. This avoids data corruption when moving data between legacy encodings (for example, EBCDIC) and modern encodings (such as UTF-8). TRANSLATION objects live in the catalog, can be referenced in CAST ... USING clauses, and are dropped with DROP TRANSLATION. Support is limited; IBM Db2 implements the feature closely to the SQL standard, while most other engines rely on built-in collations or external utilities.

SQL TRANSLATION Syntax

CREATE TRANSLATION translation_name
  FROM source_character_set
  TO   target_character_set
  WITH ( mapping_specification );

-- Remove a translation
DROP TRANSLATION translation_name;

SQL TRANSLATION Parameters

translation_name (identifier) - Name of the cataloged translation object.
source_character_set (identifier) - Existing character set to translate from.
target_character_set (identifier) - Existing character set to translate to.
mapping_specification (list) - Pairs of source code points and their target equivalents. In Db2 this is supplied by the keyword LOAD FROM codepage or via a binary mapping file.

Example Queries Using SQL TRANSLATION

-- Create a translation from an EBCDIC code page to UTF-8
CREATE TRANSLATION ebcdic_to_utf8
  FROM IBM037
  TO   UTF8
  WITH (
    0x81 = 0xC3 0xA1,  -- á
    0x82 = 0xC3 0xA9,  -- é
    ...
  );

-- Use the translation explicitly in a CAST
SELECT CAST(source_col AS CHAR(100) CHARACTER SET UTF8)
  USING ebcdic_to_utf8
FROM legacy_table;

Expected Output Using SQL TRANSLATION

The CREATE statement registers the ebcdic_to_utf8 translation in the system catalog
Subsequent queries that cast data from IBM037 to UTF8 using this translation will return correctly converted UTF-8 strings
The DROP statement removes the catalog entry

Use Cases with SQL TRANSLATION

Migrating legacy COBOL or mainframe data encoded in EBCDIC to Unicode.
Loading flat files created with a regional code page into a Unicode warehouse.
Ensuring round-trip accuracy when synchronizing heterogeneous databases that use different encodings.
Implementing custom transliteration rules not covered by built-in conversions.

Common Mistakes with SQL TRANSLATION

Assuming every database supports CREATE TRANSLATION – most do not.
Confusing TRANSLATION objects with the TRANSLATE string function.
Omitting a mapping entry, which silently results in substitution characters or errors.
Dropping a translation that is still referenced by views or routines, causing dependency failures.

First Introduced In

ISO/IEC 9075:1999 (SQL:1999)

Frequently Asked Questions

What is a SQL TRANSLATION object?

A SQL TRANSLATION is a cataloged mapping between two character sets that lets the database convert text accurately from one encoding to another.

How do I create a translation between EBCDIC and UTF-8?

Issue CREATE TRANSLATION name FROM IBM037 TO UTF8 WITH (mapping); list each source byte and its UTF-8 sequence. In Db2 you can also load a binary mapping file.

Is TRANSLATION the same as the TRANSLATE function?

No. TRANSLATION is a schema object used for character-set conversion. TRANSLATE is a string function that replaces individual characters inside a string.

What happens if my database lacks TRANSLATION support?

You must rely on built-in code-page conversions, external ETL tools, or rewrite data in a common encoding before import.