ReadVitamin

What are Attribute Functions in Teradata?

Written by admin in Friday, August 10th 2007 under IT

As the name suggests, “Attribute Functions” return descriptive information about the attributes of parameter (also known as operand) passed to it. Attribute information can be about the length of the value, data type, column title etc. Although most of the time it does takes a column reference of a table, but it need not be, it also can be a general expression except for mathematical evaluations. Some of these functions are extensions to ANSI SQL like BYTES, FORMAT and TYPE. Following are a list of attribute function available in Teradata. Normal usage of these functions is as follows;

SELECT <Function Name (parameter)>; i.e. SELECT type (date) would return DATE.

• BYTES => This function returns the number of bytes contained in the specified byte string, remember to use ‘byte‘ strings only and not character strings.

• CHARACTER_LENGTH => As the name suggests, CHARACTER_LENGTH function returns the length of a string either in logical characters or in bytes. Same as the function CHARACTERS ()

• FORMAT => Very useful function in case of display/reporting needs. It returns the declared format for the named expression. For example SELECT format (date) would give the following result;

Format (Date)

YY/MM/DD

• OCTET_LENGTH => It returns the length of string expression (CHAR or VARCHAR) in octets when the expression is converted to the named or client character set. Mostly used for international character conversions.

• TITLE => Returns the title or column heading of an expression as it appears in the heading for displayed results. If you have a column name as “Name” and you want to confirm what you know is right or not, just type the following SELECT statement. SELECT TITLE (Name) FROM <table name>

• TYPE => This one is very useful, it returns the data type defined for an expression. If you do not know the data type a particular column and want to know about it, use this function.

Popularity: 13% [?]

Our Random Articles

2 Comments

Nirupama Said in Thursday, December 6th, 2007 @6:02 am

I want to retrieve only the hour part and minute part from the date. Please can u help me in this regard?

admin Said in Thursday, December 6th, 2007 @5:49 pm

Hi Nirupama,
You can use EXTRACT function to retrive HOUR and MINUTE variables as follows
SELECT EXTRACT (HOUR from time);
SELECT EXTRACT ( MINUTE FROM time);

If you would like to have both, then concatenate them as follows:

SELECT EXTRACT (HOUR from time) || EXTRACT ( MINUTE FROM time)

Hope that helps.