TYPE Function Examples | Teradata Vantage - Examples - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

Example 1: If User Default Server Character Set is LATIN

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 2: If User Default Server Character Set is LATIN but Server Character Set for Name Column is UNICODE

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 3: Returning the types of the Name and Salary columns

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 4: Using TYPE to Request the Data Type of Two Columns

If TYPE is used to request the data type of two columns, defined as GRAPHIC and LONG VARCHAR CHARACTER SET GRAPHIC, 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 VARCHAR CHARACTER SET GRAPHIC column, the length returned is the maximum length of 32000.

Example 5: Using the TYPE Function

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 6: Applying the TYPE function to the BLOB Column

Consider the following table definition:

   CREATE TABLE images
     (imageid INTEGER
     ,imagedesc VA
RCHAR(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.