Chapter 7 – Format Functions

“Even I don’t’ wake up looking like Cindy Crawford.”

– Cindy Crawford

The FORMAT Command

In this example, we are using it for dates. All dates in Teradata are stored in the systems as an INTERGERDATE. This allows it to be read and formatted easily.

The Basics of the FORMAT Command

Format the dates for appearance on the report. The actual data doesn’t change.

Quiz – How will the Date Appear after Formatting

SELECT Current_Date (FORMAT 'mm-dd-yy') ;

How will the answer appear?

Answer to Quiz – How will the Date Appear after Formatting

SELECT Current_Date (FORMAT 'mm-dd-yy') ;

How will the answer appear?

03-20-13

Quiz – How will the Date Appear after Formatting

SELECT Current_Date (FORMAT 'mm-dd-yyyy') ;

How will the answer appear?

Answer to Quiz – How will the Date Appear after Formatting

SELECT Current_Date (FORMAT 'mm-dd-yyyy') ;

How will the answer appear?

03-20-2013

Formatting with MMM for the Abbreviated Month

How will the answer appear?

Answer to Quiz – How will the Date Appear after Formatting

How will the answer appear?

Mar-20-2013

Formatting with MMMM for the Full Month Name

How will the answer appear?

Formatting with MMMM for the Full Month

How will the answer appear?

March-20-2013

When you use mmmm with the format statement you get the month spelled out.

Formatting with DDD for the Julian Day

How will the answer appear?

Formatting with DDD for the Julian Day

How will the answer appear?

When you use ddd with the format statement you get the Julian date.

Formatting with EEE or EEEE for the Day of the Week

How will the answers appear?

EEEE for the Abbreviated or Full Day of the Week

Sun-03-20-13

Sunday-03-20-13

When you use eee with the format statement you get the first three letters of the month. When you use eeee you get the entire month spelled out..

Placing Spaces inside your Formatting Commands with a B

SELECT Current_Date (FORMAT 'eeeeBBbbMMMM'),

How will the answer appear?

Formatting Spaces with B or b

How will the answer appear?

Sunday   March

Formatting with 9

You can also format how numbers appear.
Such as in the case of a phone number.

SELECT 5133000346 (FORMAT '999-999-9999'),

How will the answer appear?

By putting in 999-999-9999, this is telling the system to put the literal numbers 5133000346 next to the SELECT into the formatting style.

Formatting with 9 Results

You can also format how numbers appear.
Such as in the case of a phone number.

SELECT 5133000346 (FORMAT '999-999-9999'),

How will the answer appear?

513-300-0346

By putting in 999-999-9999, this is telling the system to put the literal numbers 5133000346 next to the SELECT into the formatting style.

Troubleshooting when Formatted Data Overflows

Notice that we’ve taken out one of the 9s in the Format Statement

SELECT '5133000346' (FORMAT '99-999-9999'),

How will the answer appear?

The FORMAT OVERFLOW is what happens when your system doesn’t have enough spaces in the FORMAT to cover the number you are trying to format.

Troubleshooting when Formatted Data Overflows

Notice that we’ve taken out one of the 9s in the Format Statement

SELECT '5133000346' (FORMAT '99-999-9999'),

How will the answer appear?

************

The FORMAT OVERFLOW is what happens when your system doesn’t have enough spaces in the FORMAT to cover the number you are trying to format.

Formatting with X or x

You can also format letters and words!

SELECT 'ABCDE' (FORMAT 'XxX'),

How will the answer appear?

You can also FORMAT characters. Look at this example. It doesn’t matter if the X’s are capitalized or not.

Formatting with X or x Results

You can also format letters and words!

SELECT 'ABCDE' (FORMAT 'XxX'),

How will the answer appear?

ABC   

You can also FORMAT characters. Look at this example. It doesn’t matter if the X’s are capitalized or not.

Formatting with Z

The Z’s represent potential data. This tells the system that if there is a number to put in the Z’s position, then put it in. If there is not, leave it blank.

SELECT 1021.53 (FORMAT 'ZZZZZZ9.99'),

How will the answer appear?

What a ‘9’ represents in a format statement is that if there is a number in the ‘9’ position, then put it in. If there isn’t one, then you put a blank.

Formatting with Z Visual

The Z’s represent potential data. This tells the system that if there is a number to put in the Z’s position, then put it in. If there is not, leave it blank.

SELECT 1021.53 (FORMAT 'ZZZZZZ9.99'),

How will the answer appear?

1021.53

What a ‘9’ represents in a format statement is that if there is a number in the ‘9’ position, then put it in. If there isn’t one, then you put a blank.

Formatting with 9

The 9’s represent potential data. This tells the system that if there is a number to put in the 9’s position, then put it in. If there is not, leave it blank.

SELECT 1021.53 (FORMAT
'99999999.9999'),

How will the answer appear?

Formatting with 9 Visual

The 9’s represent potential data. This tells the system that if there is a number to put in the 9’s position, then put it in. If there is not, leave it blank.

SELECT 1021.53 (FORMAT
'99999999.9999'),

How will the answer appear?

00001021.5300

Formatting with $

What the $ allows you to do is to tell your formatting to place
a $ sign in front of the result set, but only at the beginning.

SELECT 1021.53 (FORMAT '$$$$$$9.99'),

How will the answer appear?

Formatting with $ Visual

What the $ allows you to do is to tell your formatting to place
a $ sign in front of the result set, but only at the beginning.

SELECT 1021.53 (FORMAT '$$$$$$9.99'),

How will the answer appear?

$1021.53

Formatting with $ and Commas

You can also use commas in your Formatting statements.

SELECT 1021.53 (FORMAT '$,$$$,$$9.99'),

How will the answer appear?

Formatting with $ and Commas Visual

You can also use commas in your Formatting statements.

SELECT 1021.53 (FORMAT '$,$$$,$$9.99'),

How will the answer appear?

$1,021.53

Formatting with $ and Commas and 9

You can also use commas in your Formatting statements.

SELECT 0.53 (FORMAT '$,$$$,$$9.99'),

How will the answer appear?

Formatting with $ and Commas and 9 with Zero Dollars

You can also use commas in your Formatting statements.

SELECT 0.53 (FORMAT '$,$$$,$$9.99'),

How will the answer appear?

$0.53

The ‘9’ sees the 0 and knows to bring it back in the answer set. The floating $ will only bring back a $ for the first character.

A Great Formatting Example

SELECT	'ABCDE'	(FORMAT 'XxX') AS Fmt_Shorter
	,2014859999	(FORMAT '999-999-9999') AS Fmt_Phone
	,1021.53	(FORMAT 'ZZZZZZ9.9999') AS Z_Press
	,991001(date)	(FORMAT 'Yyddd') AS Fmt_Julian
	,991001(date)	(FORMAT 'eee') As Weekday
	,991001	(FORMAT '$$$$,$$$.99') AS Fmt_Pay ;

There are only two things that need to be watched when using the FORMAT function. First, the data type must match the formatting character used or a syntax error is returned. So, if the data is numeric, use a numeric formatting character and the same condition for character data. The other concern is configuring the format mask big enough for the largest data column. If the mask is too short, the SQL command executes, however, the output contains a series of ************* to indicate a format overflow.

A Great Formatting Example for Day, Month and Year

All of these FORMAT requests work wonderfully if the client software is BTEQ or the Nexus Query Chameleon, but SQL Assistant has problems with formatting. After all, it is a report writer and these are report writer options. The issue is that the ODBC and SQL Assistant look at the data as data, not as a report. Since many of the formatting symbols are “characters” they cannot be numeric. Therefore, the ODBC strips off the symbols and presents the numeric data to the client for display.

A Trick to get SQL Assistant to Format Data

If a tool uses the ODBC, the FORMAT in the SELECT is ignored and the data comes back as data, not as a formatted field. This is especially noticeable with numeric data and dates.

To force tools like SQL Assistant to format the data, the software must be tricked into thinking the data is character type, which it leaves alone. This can be done using the CAST function. The Nexus Query Chameleon does not have a problem formatting.

Using the CASESPECIFIC (CS) Command in Teradata Mode

In Teradata Mode a capital ‘A’ is seen as the same in comparison as a little ‘a’. The CASESPECIFIC command, which is abbreviated as CS will make sure the case of a letter is examined.

Using NOT CASESPECIFIC (CS) in ANSI Mode

In ANSI Mode a capital ‘A’ is seen as different in comparison as a little ‘a’. The NOT CASESPECIFIC command, which is abbreviated as NOT CS will make sure the case of a letter is examined and returned whether or not they have the same case.

Using the LOWER Command

In ANSI Mode a capital ‘A’ is seen as different in comparison as a little ‘a’. The LOWER command can be used to make sure the case of a column is lowered.

Using the UPPER Command

In ANSI Mode a capital ‘A’ is seen as different in comparison as a little ‘a’. The UPPER command can be used to make sure the case of a column is UPPERED.