The Teradata SUBSTRING or SUBSTR function is one of the Teradata string functions, and it is used to cut a substring from a string based on its position.
SUBSTR or SUBSTRING will work the same in Teradata. But the syntax may be different.
We use ANSI syntax for Teradata SUBSTRING and Teradata syntax for Teradata SUBSTR. The ANSI syntax is designed to compatible with other database systems.
Argument Types and Rules
SUBSTRING and SUBSTR operate on the following types of arguments:
If the string_expression argument is numeric, then User-defined type (UDT) are implicitly cast to any of the following predefined types:
To define an implicit cast for a UDT, we use the CREATE CAST statement and specify the AS ASSIGNMENT clause.
Implicit type conversion of UDTs for system operators and functions, including SUBSTRING and SUBSTR, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE.
Result Type and Attributes
Here are the default result type and attributes for SUBSTR and SUBSTRING, such as:
If the string argument is a:
In ANSI mode, the value of n for the resulting BLOB(n), VARBYTE(n), CLOB(n), or VARCHAR(n) is the same as the original string.
In Teradata mode, the value of n for the result type depends on the number of characters or bytes in the resulting string. To get the data type of the resulting string, we can use the TYPE function.
Difference between SUBSTRING and SUBSTR
The SUBSTR function is the original Teradata substring operation. It is written to be compatible with DB/2.
The SUBSTRING function 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 costs.