SUBSTRING and Numeric Data - Teradata

Both substring functions are fairly straightforward when working with character data. However, they are not limited to character columns. They can also be used with numeric data. The caution here is that these functions are designed to work with character data. Therefore, all numeric data is automatically converted to character before it can be used. The biggest issue is that Teradata uses the same type of implicit conversion that was seen in an earlier chapter. As a reminder, the database uses the full length required to store the largest possible data value for each numeric data type. The length must include one additional character for the sign in the event of a negative (−) number.
A value like −32,000 as a SMALLINT is "−32000" with a length of six characters (5 digits + sign). At the same time, a value like 128 is also assumed to be a SMALLINT as "128" with a length of six characters. The difference here is that the first three digits are spaces (sign is assumed positive and becomes a space).

However, if the −32,000 is stored in a column of INTEGER data type, it has a character length of eleven and is internally stored as "−32000" with five leading spaces. This must be taken into consideration when using substring functions with numeric data.

The next SELECT demonstrates this conversion using literal data:


This example demonstrates the attention that must be applied when using a numeric data type with character functions. A safer way to do the above processing is the following SELECT:


As seen in these examples, it is a good idea to use CAST to explicitly convert the numeric data to character data first. Then, the SUBSTRING or SUBSTR operations perform as expected.

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

Teradata Topics