15.00 - Byte 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)

Byte Conversion

Purpose

Converts a byte expression to a different data definition.

CAST Syntax

where:

 

Syntax element …

Specifies …

byte_expression

an expression in byte format to be cast to a different data definition.

byte_data_type

the new byte type to which byte_expression is to be converted.

UDT_data_type

a UDT that has a cast definition that casts the byte type to the UDT.

To define a cast for a UDT, use the CREATE CAST statement. For details on CREATE CAST, see SQL Data Definition Language.

data_attribute

one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

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

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    byte_expression

    an expression in byte format to be cast to a different byte data definition.

    byte_data_type

    an optional byte type to which byte_expression is to be converted.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

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

    Conversions Where Source and Target Types Differ in Length

    If the length specified by byte_data_type is less than the length of byte_expression, bytes beyond the specified length are truncated. No error is reported.

    If byte_data_type is fixed‑length and the length is greater than that of byte_expression, bytes of value binary zero are appended as required.

    Supported Source and Target Data Types

    Teradata Database supports byte data type conversions according to the following table.

     

    Source Data Type

    Target Data Type

    Allowable Conversions

    BYTE

  • BYTE
  • VARBYTE
  • BLOB
  • Implicit
  • Explicit using CAST and Teradata conversion syntax
  • VARBYTE

    BLOB

    BYTE

    UDT1

  • Implicit
  • Explicit using CAST
  • VARBYTE

    BLOB

    UDTa

  • BYTE
  • VARBYTE
  • BLOB
  • Implicit
  • Explicit using CAST and Teradata conversion syntax

  • 1

    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.


    Rules for Implicit Byte-to-UDT Conversions

    Teradata Database performs implicit Byte-to-UDT conversions for the following operations:

  • UPDATE
  • INSERT
  • Passing arguments to stored procedures, external stored procedures, UDFs, and UDMs
  • Specific system operators and functions identified in other sections of this book, unless the DisableUDTImplCastForSysFuncOp field of the DBS Control Record is set to TRUE
  • Performing an implicit Byte-to-UDT data type conversion requires a cast definition (see “Usage Notes”) that specifies the following:

  • the AS ASSIGNMENT clause
  • a BYTE, VARBYTE, or BLOB source data type
  • The source data type of the cast definition does not have to be an exact match to the source of the implicit type conversion.

    If multiple implicit cast definitions exist for converting different byte types to the UDT, Teradata Database uses the implicit cast definition for the byte type with the highest precedence. The following list shows the precedence of byte types in order from lowest to highest precedence:

  • BYTE
  • VARBYTE
  • BLOB
  • Using HASHBUCKET to Convert a BYTE Type to an INTEGER Type

    You can use the HASHBUCKET function to convert a BYTE(1) or BYTE(2) type to an INTEGER type. For details, see “Using HASHBUCKET to Convert a BYTE Type to an INTEGER Type” on page 899.

    Example : Explicit Conversion of BLOB to VARBYTE

    Consider the following table definition:

       CREATE TABLE large_images 
         (id INTEGER
         ,image BLOB);

    The following statement casts the BLOB column to a VARBYTE type, and uses the result as an argument to the POSITION function:

       SELECT POSITION('FFF1'xb IN (CAST(image AS VARBYTE(64000))))
       FROM large_images
       WHERE id = 5;

    Example : Implicit Conversion of VARBYTE to BLOB

    Consider the following table definitions:

       CREATE TABLE small_images 
         (id INTEGER
         ,image1 VARBYTE(30000)
         ,image2 VARBYTE(30000));
       
       CREATE TABLE large_images 
         (id INTEGER
         ,image BLOB);

    Teradata Database performs a VARBYTE to BLOB implicit conversion for the following INSERT statement:

       INSERT large_images
       SELECT id, image1 || image2
       FROM small_images;

    Related Topics

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