CAST Teradata

Compatibility: ANSI

Under most conditions, the data types defined and stored in a table should be appropriate. However, sometimes it is neither convenient nor desirable to use the defined type. Data can be converted from one type to another by using the CAST function. As long as the data involved does not break any data rules (i.e. placing alphabetic or special characters into a numeric data type) the conversion works. The name of the CAST function comes from the Convert And STore operation that it performs.

Care must also be taken when converting data to manage any potential length issues. In Teradata mode, truncation occurs if a length is requested that is shorter than the original data. However, in ANSI mode, an SQL error is the result because ANSI says, "Thou shalt not truncate data."

The basic syntax of the CAST statement follows:

Examples using CAST:

These are only some of the potential conversions and are primarily here for illustration of how to code a CAST. The CAST could also be used within the WHERE clause to control the length characteristics or the type of the data to compare.

Again, when using the CAST in ANSI mode, any attempt to truncate data causes the SQL to fail because ANSI does not allow truncation.

The next SELECT uses literal values to show the results of conversion:

1 Row Returnedliteral values to show the results of conversion

In the above example, the first CAST truncates the five characters (left to right) to form the single character ‘A’. In the second CAST, the integer 128 is converted to three characters and left justified in the output. The 127 was initially stored in a SMALLINT (5 digits - up to 32767) and then converted to an INTEGER. Hence, it uses 11 character positions for its display, ten numeric digits and a sign (positive assumed) and right justified as numeric.

The value of 121.53 is an interesting case for two reasons. First, it was initially stored as a DECIMAL as 5 total digits with 2 of them to the right of the decimal point. Then it is converted to a SMALLINT using CAST to remove the decimal positions. Therefore, it truncates data by stripping off the decimal portion. It does not round data using this data type. On the other hand, the CAST in the fifth column called Rounder is converted to a DECIMAL as 3 digits with no digits (3,0) to the right of the decimal, so it will round data values instead of truncating. Since .53 is greater than .5, it is rounded up to 122.

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

Teradata Topics