The Teradata SUBSTRING Function / SUBSTRING in Teradata

The Teradata SUBSTRING Function

teradata substring

The Teradata SUBSTRING or SUBSTR function is one of the Teradata string functions and used to cut a substring from a string based on its position.

You can use ANSI syntax or Teradata syntax. The ANSI syntax is recommended to remain compatible with other database systems.

ANSI Syntax:

SUBSTRING(str FROM pos [FOR count]);
Parameters:
Mandatory:
str is the string expression from which the substring is taken.
pos is the starting position in str
Optional:
[FOR] 
count is the length of the substring (optional). FOR count is omitted, the substring starts at position pos and takes all remaining characters from the string to the end.

Teradata Syntax:

SUBSTR(str,pos[,count]);
Parameters: 
Mandatory:
str is the string expression from which the substring is taken.
pos is the starting position in str
Optional:
[,count] 
count is the length of the substring (optional). FOR count is omitted, the substring starts at position pos and takes all remaining characters from the string to the end.

Allowed Argument Types

  • Character
  • Byte
  • Numeric
  • User-Defined Type (UDT):
    are implicitly cast to one of the following types: CHARACTER, NUMERIC, DATE, BYTE

Data Type of Return Value

  • If str is BLOB then BLOB
  • Any Byte String (except BLOB) returns VARBYTE(n)
  • CLOB returns CLOB(n)
  • Character String or Numeric Value except for CLOB returns VARCHAR(n)

In ANSI mode, the value of n for each DATATYPE(n) is equal to the value of the original string.

In Teradata mode, the value of n depends on the number of characters/bytes in the result string.

Examples for Teradata SUBSTRING

Here is an example for the SUBSTRING function applied to a character string column:

SELECT * FROM Country WHERE SUBSTRING (country_desc FROM 1 FOR 3) = 'TUN';

Here is an example for the Teradata SUBSTRING function applied to a CLOB column:

SELECT SUBSTRING (myCLOB FROM 100 FOR 120000) FROM TheCLOBTable; 

Tuning with Teradata SUBSTRING

Both SUBSTRING and SUBSTR store only the requested characters/bytes in the spool, and not the entire column. Therefore it may make sense to use this function early in the ETL process (e.g. in a volatile table) if only a part of a column is needed.

DWH Pro Admin
 

>