TRIM - Teradata

Compatibility: ANSI

The TRIM function is used to eliminate space characters from fixed length data values. It has the ability to get rid of trailing spaces, those after the last non-space character as well as leading spaces, those before the first data character.

The following are the four different syntax options for the TRIM function:

TRIM( TRAILING FROM <column-name> )

The TRIM function does not affect spaces that are imbedded between actual characters. It eliminates only those at the beginning or at the end of a string, never in the middle.

Sometimes, it is necessary to shorten fixed length data. This may be to save spool space or to combine multiple columns into a single display. This is where the TRIM function can be of great assistance. At the end of this chapter, the concatenation or combining of columns is shown. First, it is important to understand the option available with TRIM

The following table is used to demonstrate the TRIM function. Although the Employee table can be used, this sample table contains data with leading, trailing and imbedded spaces. The imbedded spaces in the middle of the data are to show that they are not affected by the TRIM function.

TRIM function

By using the TRIM function inside the CHARACTERS function, they work together to eliminate the desired spaces and count all characters that remain in the data. As seen previously, the CHARACTERS function always returns the number of bytes equal to the full size of a fixed length character column because of the space characters.

Therefore, there is a need to eliminate the spaces to obtain the actual length of the data. By putting the TRIM function inside the CHARACTERS function, they work together to count the actual characters without the insignificant spaces.

The next SELECT uses the fixed length column from the above table to produce an answer set that includes the count of the characters stored in the column, the trimmed data and the length of the trimmed data:

SELECT Column1 /* column1 is CHAR(10) */ ,CHAR( column1 ) (TITLE 'Ct1'),TRIM(column1 ) ) AS Both_Gone ,CHAR( TRIM(column1 )) (TITLE 'Ct2'),TRIM(TRAILING FROM column1 ) AS No_Trail ,CHAR( TRIM(TRAILING FROM column1 ) ) (TITLE 'Ct3') FROM Fixed_Length_table ; 4 Rows Returnedtrimmed data and the length of the trimmed data

In this example, the TRIM function is used to eliminate the trailing spaces, following the last non-space character. By default, the TRIM function eliminates the trailing and the leading spaces. In none of the values did it eliminate the imbedded spaces.

These two are equivalent coding: TRIM

The TRIM function is helpful anytime the normal storage of spaces in a fixed length column needs to be eliminated for the output or evaluation.

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

Teradata Topics