15.00 - Character-to-Character Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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.