SUBSTR Teradata

Compatibility: Teradata Extension

The SUBSTR function is the original Teradata substring operation. It was written to be compatible with DB/2. Like the newer ANSI SUBSTRING function, it can be used in the SELECT list to return any portion of the character data stored in a column to a client or in the WHERE clause.

When using the SUBSTR function, like SUBSTRING, the name of the column needs to be provided along with the starting character location and the length or number of characters to return. The main difference is that commas are used as delimiters between these three parameters instead of FROM and FOR.

The syntax of the SUBSTR follows:

SUBSTR ( <column-name>, <start-location> [ , <length> ] )

The following produces exactly the same output as the SUBSTRING using the original SUBSTR Teradata syntax:

1 Row Returnedsyntax of the SUBSTR

Like the SUBSTRING function, the length is optional. When it is not included, all remaining characters to the end of the column are returned. In the earlier releases of Teradata, the SUBSTR was much more restrictive in the values allowed. This situation increased the chances of the SQL statement failing due to unexpected data or values.

Again, both SUBSTRING and SUBSTR allow for partial character data strings to be returned, even in ANSI mode. Like CAST, these functions only store the requested data in spool, not the entire column. Therefore, the amount of spool space required can be reduced or tuned using the substring functions.

In the current release, the SUBSTR is more compatible and tolerant regarding the parameter values passed to them, like the newer SUBSTRING. However, SUBSTRING is the ANSI standard and therefore, is the better choice between these two functions.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics