Example: Converting the Server Character Set of a Character Literal - Teradata Vantage

The data type and server character set of a character literal is always VARCHAR(n) CHARACTER SET UNICODE, where n is the length of the literal.

Certain situations may require you to convert a character literal from UNICODE to a different server character set.

For example, suppose you want to compare the hexadecimal representation of two character strings. To get the hexadecimal representation of a character string, you can use CHAR2HEXINT.

Suppose one of the character strings you want to compare is the character literal 'a'. The following statement returns the hexadecimal representation of 'a':

SELECT CHAR2HEXINT('a');

The result is '0061', the hexadecimal representation of 'a' in the UNICODE server character set, which uses two bytes per character.

Suppose the other character string you want to compare is in a column defined as CHAR(1) CHARACTER SET LATIN:

CREATE TABLE table1
  (column1 CHAR(1) CHARACTER SET LATIN
  ,column2 INTEGER);
INSERT INTO table1 ('a', 1001);

The following statement returns the hexadecimal representation of the string in column1:

SELECT CHAR2HEXINT(column1);

The result is '61', the hexadecimal representation of 'a' in the LATIN server character set, which uses one byte per character.

The following query compares the two strings:

SELECT column2 FROM table1
WHERE CHAR2HEXINT('a') = CHAR2HEXINT(column1);

The result is an empty set, because the result of CHAR2HEXINT('a') is '0061' and the result of CHAR2HEXINT(column1) is '61'.

For situations such as this, you can use TRANSLATE to convert the server character set of one of the character strings to match the server character set of the other character string. For example:

SELECT column2 FROM table1
WHERE CHAR2HEXINT( TRANSLATE('a' USING UNICODE_TO_LATIN) ) =
CHAR2HEXINT(column1);

returns:

    column2
-----------
       1001