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: |
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: |
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 |
|
|
VARBYTE |
||
BLOB |
||
BYTE |
UDT1 |
|
VARBYTE |
||
BLOB |
||
UDTa |
|
|
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:
Performing an implicit Byte-to-UDT data type conversion requires a cast definition (see “Usage Notes”) that specifies the following:
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:
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.