In many cases, it is acceptable to display data directly as it is stored in the database. However, there are times when it is not acceptable and the character data must be temporarily transformed. It might need shortening or something as simple as eliminating undesired spaces from a value. The tools to make these changes are discussed here.
Earlier, we saw the CAST function as a technique to convert data. It can be used to truncate data unless running in ANSI mode, which does not allow truncation. These functions provide an alternative to using CAST, because they do not truncate data. Instead, they allow a portion of the data to be returned. This is a slight distinction, but enough to allow the processing to provide some interesting capabilities.
We will examine the CHARACTERS, TRIM, SUBSTRING, SUBSTR, POSITION and INDEX functions. Alone, each function provides a capability that can be useful within SQL. However, when combined, they provide some powerful functionality.
This is an excellent time to remember one of the primary differences between ANSI mode and Teradata mode. ANSI mode is case sensitive and Teradata mode is not. Therefore, the output from most of these functions is shown here in both modes.