Character-to-Character Conversion - Teradata Database

Character-to-Character Conversion

Purpose

Shortens or expands output character strings.

CAST Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a different character data definition.
character_data_type	the new data type to which character_expression is to be converted.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE CHARACTER SET

ANSI Compliance

CAST is ANSI SQL:2011 compliant, provided the syntax does not specify any data attributes.

Teradata Conversion Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a different character data definition.
character_data_type	an optional character type to which character_expression is to be converted.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE CHARACTER SET If the syntax specifies character_data_type, CHARACTER SET can only appear after character_data_type.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Implicit Character-to-Character Conversion

CLOB types can only be converted to or from CHAR or VARCHAR types. For example, implicit conversion is performed on CLOB data that is inserted into a CHAR or VARCHAR column.

Comparisons of strings (both fixed‑ and variable‑length) require operands of equal length. The following table shows that the shorter string is converted by being padded on the right.

THIS expression …	IS converted to …	AND the result is …
'x'='x '	'xΔ'='x '	TRUE
'x'='xx'	'xΔ'='xx'	FALSE

where Δ is a pad character.

If a character is not in the repertoire of the target character set, an error is reported.

For rules on the effect of server character sets on character conversion, see “Implicit Character-to-Character Translation” on page 603.

CAST Syntax Usage Notes

The server character set of character_expression must have the same server character set as the target data type.

If CAST is used to convert data to a character string and non-pad characters would be truncated, an error is reported.

Teradata Conversion Syntax Usage Notes

The server character set of character_expression can be changed to a different server character set specified as data_attribute, where data_attribute is the CHARACTER SET phrase.

This is not the recommended way to perform this translation. Instead, use the TRANSLATE function. For information, see “TRANSLATE” on page 1256.

General Usage Notes

If the source string (CHAR, VARCHAR, or CLOB) is longer than the target data type (CHAR, VARCHAR, or CLOB), excess characters are truncated.

IF the session doing an INSERT or UPDATE is in this mode …	AND non-pad characters would be truncated to store character values in a table, THEN …
ANSI	an error is reported.
Teradata	no error is reported.

Pad characters are trimmed or appended, according to the following rules:

IF the source string data type is …	AND it is …	AND the target data type is …	THEN …
CHAR	longer than the target	CLOB or VARCHAR	any trailing pad characters are trimmed.
CHAR, VARCHAR, or CLOB	shorter than the target	CHAR	trailing pad characters are appended to the target.
CHAR	all pad characters	CLOB or VARCHAR	the field is truncated to zero length.

Examples  

Following are examples of character to character conversions:

Character String	String Length	Character Description	Conversion Result	Converted Length
'HELLO'	5	CHAR(3)	'HEL', if session is in Teradata mode	3
			Error, if session is in ANSI mode
'HELLO'	5	CHAR(7)	'HELLO  '	7
'HELLO'	5	VARCHAR(7)	'HELLO'	5
'HELLO  '	7	VARCHAR(6)	'HELLO '	6
'HELLO  '	7	VARCHAR(3)	'HEL', if session is in Teradata mode	3
			Error, if session is in ANSI mode

Related Topics

For details on data types and data attributes, see SQL Data Types and Literals.