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.
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 Returned
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.