Teradata SUBSTRING vs SUBSTR: Syntax, Parameters, Advantages

Teradata SUBSTR Or SUBSTRING?

The SUBSTR function in Teradata is a highly effective and adaptable method of manipulating strings. It enables the extraction of specific text segments from an input string using their designated start and end positions.

This function has two syntax variations: ANSI (Teradata SUBSTRING) and Teradata-specific (Teradata SUBSTR). The ANSI syntax is usually favored because it guarantees compatibility across different database systems. Since most contemporary database systems comply with ANSI standards, this syntax can simplify the migration process when moving between systems.

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). If 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). If 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 DATATYPE(n) is equivalent to the original string’s value.

The value of “n” in Teradata mode is based on the length of the resulting string in terms of characters or bytes.

Examples for Teradata SUBSTRING

This illustrates the SUBSTRING function used on a column containing character strings.

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

This demonstrates the use of the Teradata SUBSTRING function on a CLOB column.

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

Tuning with Teradata SUBSTRING

The SUBSTRING and SUBSTR functions selectively store designated characters or bytes in the spool, rather than the entire column, resulting in improved efficiency. Therefore, incorporating these functions early in the ETL (Extract, Transform, Load) process, particularly in a volatile table, can prove advantageous when a partial column suffices.

Please find additional examples by following this link:

https://forgetcode.com/teradata/1619-substr