The OCTET_LENGTH function is used to count the number of characters stored in a data column. It is another 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 OCTET_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 OCTET_LENGTH function counts every space.
The syntax of the OCTET_LENGTH function:
OCTET_LENGTH ( <column-name> )
To use the OCTET_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 OCTET_LENGTH function.
The next SELECT demonstrates how to code using the OCTET_LENGTH function in both the SELECT list as well as in the WHERE, plus the answer set:4 Rows Returned
If there are leading and imbedded spaces stored within the column, the OCTET_LENGTH function counts them as valid or significant data characters.
As mentioned earlier, the OCTET_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 Returned
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 OCTET_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.
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|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.