Teradata Substring
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.
Syntax
Or
Argument Types and Rules
SUBSTRING and SUBSTR operate on the following types of arguments:
- Character
- Byte
- Numeric
If the string_expression argument is numeric, then User-defined type (UDT) are implicitly cast to any of the following predefined types:
- Character
- Numeric
- Byte
- DATE
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:
- BLOB, then the result type is BLOB(n).
- Byte string other than BLOB, the result type is VARBYTE(n).
- CLOB, then the result type is CLOB(n).
- Numeric or character string other than CLOB, the result type is VARCHAR(n).
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.
- It can be used in the SELECT list to return any portion of the character that data stored in a column to a client or in the WHERE clause.
- When we use the SUBSTR function, such as 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 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.
- Both SUBSTRING and SUBSTR allow for partial character data strings to be returned, even in ANSI mode. These functions only store the requested data in a spool, not the entire column. Therefore, the amount of spool space required can be reduced or tuned using the substring functions.
- The SUBSTR is more compatible and tolerant regarding the parameter values passed to them, like the newer SUBSTRING.
- The SUBSTRING is the ANSI standard, and therefore, it is the better choice between these two functions.