15.00 - CAST in Explicit Data Type Conversions - 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)
Last Update
2018-09-24

CAST in Explicit Data Type Conversions

Purpose

Converts an expression of a given data type to a different data type or the same data type with different attributes.

Teradata SQL supports two different syntaxes for CAST functionality, only one of which is ANSI SQL:2011 compliant.

Syntax

where:

 

Syntax element …

Specifies …

expression

an expression with known data type to be cast as a different data type.

ansi_sql_data_type

the new data type for expression.

data_definition_list

the new data type or data attributes or both for expression.

ANSI Compliance

The form of CAST syntax that specifies ansi_sql_data_type is ANSI SQL:2011 compliant.

The form of CAST syntax that specifies data_definition_list is a Teradata extension to the ANSI SQL:2011 standard. Note that when data_definition_list consists solely of an ANSI data type declaration, then this form of the syntax is also ANSI-compliant.

Usage Notes

The ANSI SQL:2011 compliant form can be used to convert data types in either ANSI‑compliant SQL statements or Teradata SQL statements.

The Teradata extended syntax is more general. It allows a type declaration or data attributes or both. For more information on data types and attributes, see SQL Data Types and Literals.

Avoid using the extended form of CAST for any application intended to be ANSI-compliant and portable.

CAST functions identically in both ANSI and Teradata modes.

When converting DateTime data types, you can use the AT clause to specify the time zone used for the CAST. You can specify the source time zone, a specific time zone displacement, or the current session time zone. For more information, see the section on converting the specific data type, for example, TIMESTAMP-to-DATE Conversion.

CAST does not convert the following data type pairs:

  • Numeric to character, if the server character set is GRAPHIC.
  • Character expressions having different server character sets.
  • To make such a conversion, use the TRANSLATE function (see “TRANSLATE” on page 1256).

  • Byte (BYTE, VARBYTE, and BLOB) to any data type other than UDT or byte, and data types other than byte or UDT to byte.
  • CLOB to any data type other than UDT or character, and data types other than character or UDT to CLOB.
  • For information on casting to and from geospatial types, see SQL Geospatial Types.

    Data type conversions involving UDTs require appropriate cast definitions for the UDTs. To define a cast for a UDT, use the CREATE CAST statement. For more information on CREATE CAST, see SQL Data Definition Language.

    Character Truncation Rules

    The following rules apply to character strings:

     

    IF the string is cast in this mode …

    THEN it is truncated of …

    ANSI

    trailing pad character spaces to achieve the desired length. Truncation of other characters, or part of a multibyte character, returns an error.

    Teradata

    trailing characters to achieve the desired length.

    Truncation on Kanji1 character data types containing multibyte characters might result in truncating one byte of the multibyte character.

    Server Character Set Rules

    When data_definition_list specifies a data type of CHARACTER (CHAR) or CHARACTER VARYING (VARCHAR) and does not specify a CHARACTER SET clause to indicate which server character set to use, then the resulting server character set is as follows:

     

    IF the data type of expression is …

    THEN the server character set of the resulting characters is …

    non-character

    the user default server character set.

    character

    the server character set of expression.

    Numeric Overflow, Field Mode, and CAST

    Numeric overflows are handled differently depending on whether you are running ANSI or Teradata mode, and whether you are running in Field Mode or not.

    Field Mode is not ANSI SQL:2011 compatible. In Field Mode, conversion to a numeric or decimal data type that results in a numeric overflow is returned as asterisks (‘***’) rather than an error message.

    Record and Indicator Modes do not behave in this manner and return an error message.

    Related Topics

    For further rules that apply to the conversion between specific data types, for example, numeric-to numeric or character-to-numeric, see the appropriate succeeding topic in this chapter.

    Examples

    The following examples illustrate how to perform data type conversions using CAST.

    Example  

    Using ANSI CAST syntax:

       SELECT ID_Col, Name_Col
       FROM T1
       WHERE Int_Col = CAST(SUBSTRING(Char_Col FROM 3 FOR 3) AS INTEGER);

    Example  

    Using ANSI CAST syntax:

       SELECT CAST(SUBSTRING(Char_Col FROM 1 FOR 2) AS INTEGER),
          CAST(SUBSTRING (Char_Col FROM 3 FOR 3) AS INTEGER) 
       FROM T1;

    Example  

    Using Teradata extensions to the ANSI CAST syntax:

       CREATE TABLE t2 (f1 TIME(0) FORMAT 'HHhMIm');
       
       INSERT t2 (CAST('15h33m' AS TIME(0) FORMAT 'HHhMIm'));
       
       SELECT f1 FROM t2;

    The result from the SELECT statement is:

           f1
       ------
       15h33m