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.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

Teradata Syntax:

Get the next issue by email.

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

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.