CHARACTERS Function Teradata

Compatibility: Teradata Extension

The CHARACTERS function is used to count the number of characters stored in a data column. It is easiest to use and the most helpful when the characters being counted are stored in a variable length as a VARCHAR column. A VARCHAR stores only the characters input and no trailing spaces after the last non-space character.

When referencing a fixed length CHAR column, the CHARACTERS 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 CHARACTERS function counts every space.

The syntax of the CHARACTERS function:

CHARACTERS ( <column-name> )
CHAR ( <column-name> )

To use the CHARACTERS (can be abbreviated as CHAR) 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 Employee table is used to demonstrate the functions in this. The contents of this table is listed below:

Employee table is used to demonstrate the functions

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

4 Rows Returnednext SELECT demonstrates how to code using the CHAR function

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

The answer is exactly the same using CHAR in the SELECT list and the alias in the WHERE instead of repeating the CHAR function:

4 Rows ReturnedWHERE instead of repeating the CHAR function

As mentioned earlier, the CHAR 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 ReturnedCHAR data by retrieving the last name

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 CHAR 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 Protection Status

Teradata Topics