CHARACTER_LENGTH Function Teradata

Compatibility: ANSI

The CHARACTER_LENGTH function is used to count the number of characters stored in a data column. It is the ANSI equivalent of the Teradata CHARACTERS function available in V2R4. Like CHARACTERS, it's easiest to use and the most helpful when the characters being counted are stored in a variable length VARCHAR column. A VARCHAR stores only the characters input and no trailing spaces.

When referencing a fixed length CHAR column, the CHARACTER_LENGTH function always returns a number that represents the maximum number of characters defined. This is because the database must store the data and pad to the full length using literal spaces. A space is a valid character and therefore, the CHARACTER_LENGTH function counts every space.

The syntax of the CHARACTER_LENGTH function:

CHARACTER_LENGTH ( <column-name> )

To use the CHARACTER_LENGTH function, simply pass it a column name. When referenced in the SELECT list, it displays the number of characters. When written into the WHERE clause, it can be used as a comparison value to decide whether or not the row should be returned.

The contents of the same Employee table above is also used to demonstrate the CHARACTER_LENGTH function.

The next SELECT demonstrates how to code using the CHARACTER_LENGTH function in both the SELECT list as well as in the WHERE, plus the answer set:

4 Rows ReturnedCHARACTER_LENGTH

If there are leading and imbedded spaces stored within the column, the CHARACTER_LENGTH function counts them as valid or significant data characters.

As mentioned earlier, the CHARACTER_LENGTH function works best on VARCHAR data. The following demonstrates its result on CHAR data by retrieving the last name and the length of the last name where the first name contains more than 7 characters:

4 Rows ReturnedCHARACTER_LENGTH function

Again, the space characters are present in the data and therefore counted. Hence, all the last names are 20 characters long. The comparison is on the first name but the display is based entirely on the last name.

The CHARACTER_LENGTH function is helpful for determining demographic information regarding the VARCHAR data stored within the Teradata database. However, sometimes this same information is needed on fixed length CHAR data. When this is the case, the TRIM function is helpful.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics