Teradata SUBSTRING Tricks

Teradata SUBSTR Or SUBSTRING?

The Teradata SUBSTRING or SUBSTR function is one of the Teradata string functions used to return a substring from an input string. We define the substring using a start and an end position.

We can use the ANSI syntax (Teradata SUBSTRING) or the Teradata syntax (Teradata SUBSTR). The ANSI syntax is preferred as it ensures compatibility with many other database systems. Most database systems today are ANSI compatible, making migration easier during a changeover.

ANSI Syntax:

SUBSTRING(str FROM pos [FOR count]);
Parameters:
Mandatory are:
str is the string expression from which the substring is taken.
pos is the starting position in str
Optional is:
[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 original string’s value.

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 of 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 of 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, 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.

Teradata Docs: SUBSTRING