15.00 - TYPE - 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

TYPE

Purpose  

Returns the data type defined for an expression.

Syntax  

where:

 

Syntax element …

Specifies …

expression

the expression for which the data type is to be returned.

ANSI Compliance

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

Result Type and Attributes

TYPE returns a CHAR(n) character string that contains the name of the data type of the expression.

When the argument is a function or operation, TYPE returns a character string that contains the result type of the function or operation. For rules on the result type for an operation or function, refer to the documentation for the specific function or operation.

The result type, character set, format, and title for TYPE appear in the following table.

 

Data Type

Format

Title

CHAR(n) CHARACTER SET LATIN

X(39)

Type(named_expression)

For a list of the supported data types, see SQL Data Types and Literals. For information on geospatial types, see SQL Geospatial Types.

Character Type Arguments

If the server character set for a character type argument is different from the user default server character set, then the resulting character string also contains the CHARACTER SET phrase and the name of the server character set for the argument.

For examples, see “Example 1” and “Example 2” on page 302.

Example  

Consider the Name column in the following table definition:

   CREATE TABLE Employee
     (EmployeeID INTEGER
     ,Name       CHARACTER(30) CHARACTER SET LATIN
     ,Salary     DECIMAL(8,2));

If the user default server character set is LATIN, then the character string that TYPE returns for the Name column does not contain the CHARACTER SET phrase.

   SELECT TYPE(Employee.Name);
   
   Type(Name)
   ----------
   CHAR(30)

Example  

If the user default server character set is LATIN, but the server character set for the Name column is UNICODE, then the result string contains the CHARACTER SET phrase.

   CREATE TABLE Employee
     (EmployeeID INTEGER
     ,Name VARCHAR(30) CHARACTER SET UNICODE
     ,Salary     DECIMAL(8,2));
   
   SELECT TYPE(Employee.Name);
   
   Type(Name)
   ---------------------------------
   VARCHAR(30) CHARACTER SET UNICODE

Example  

The following statement returns the types of the Name and Salary columns:

   SELECT TYPE(Employee.Name), TYPE(Employee.Salary);
 
   Type(Name)   Type(Salary)
   -----------  ------------
   VARCHAR(30)  DECIMAL(8,2) 
   

Example  

If TYPE is used to request the data type of two columns, defined as GRAPHIC and LONG VARGRAPHIC, respectively, the result is as follows.

  TYPE(GColName) ­          ­      TYPE(LVGColName)
  -----------------------------  ------------------------------------
  CHAR(4) CHARACTER SET GRAPHIC  VARCHAR(32000) CHARACTER SET GRAPHIC

In the case of a LONG VARGRAPHIC column, the length returned is the maximum length of 32000.

Example  

Consider the following TYPE function.

   SELECT TYPE(SUBSTR(Employee.Name,3,2));

The result type of SUBSTR depends on the session mode.

If the session is set to ANSI mode, the returned result is as follows:

   Type(Substr(Name,3,2))
   ----------------------
   VARCHAR(30)

If the session is set to Teradata mode, the returned result is as follows:

   Type(Substr(Name,3,2))
   ----------------------
   VARCHAR(2)

Example  

Consider the following table definition:

   CREATE TABLE images
     (imageid INTEGER
     ,imagedesc VARCHAR(50)
     ,image BLOB(2K))
   UNIQUE PRIMARY INDEX (imageid);

The following statement applies the TYPE function to the BLOB column:

   SELECT TYPE(images.image) FROM images;

The result is:

   Type(image)
   -----------
   BLOB(2048)

Note that the result is a normal integer length, and does not use the K option that was used to define the BLOB column the CREATE TABLE statement.