15.00 - Byte Conversion - Teradata Database

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

---

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.