Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 53 54 55 56 57 58 < 59 > 60 61 62 63 64 65 .. 241 >> Next


5-89
Data Definition

Numeric to Character

T1.Field1 is INTEGER with the default format ‘10(9)’. The user has Example 1 values such as 123456, with no values of over 999999.

SELECT Field1(CHAR(8)) FROM T1;

returns ‘ 123’ for the value 123456, where the result includes 5

leading blanks and truncates significant digits.

Example 2

Based on the following description of Salary data:

Salary (DECIMAL(8,2), FORMAT '$$$,$$9.99')

data is converted as illustrated in the following table (b = blank space):

Data Conversion________ Result_______

20000.00 Salary(CHAR(10)) '$20,000.00'

9000.00 Salary(CHAR(10)) 'b$9,000.00'

20000.00 Salary(FORMAT'9(5)') (CHAR (5)) '20000'

9000.00 CAST(Salary AS CHAR(10)) '$9,000.00b'

The resultant character string is either extended with blanks or truncated to conform to the given character description.

In order for a numeric type value to be converted to a character string, the character description must contain a data type declaration. A FORMAT phrase, by itself, cannot be used to convert a numeric type value to a character type value. The phrase only controls how the resultant value is displayed.

If a FORMAT phrase is not included in the character description, then the FORMAT associated with the original numeric value determines how data is displayed.

The data definition phrases form of numeric to character conversion uses explicit or default FORMATs to convert to a character representation. It then truncates or pads with blanks, depending what length the character string dictates. This can lead to a loss of significance.

Suppose EmpNo was defined as SMALLINT with the default Example 3 format of ‘9(6)’. Suppose a value in EmpNo is 12501. The statement:

SELECT EmpNo(CHAR(5)) FROM Employee

returns the ‘1250’, with a leading blank and the low order digit missing. The CAST function used for the same conversion, converts to the character representation of the numeric value, trims leading blanks, and finally truncates or pads on the right, i.e.,

SELECT CAST (EmpNo AS CHAR(5) ) FROM Employee

returns ‘12501’.

5-90

Teradata RDBMS for UNIX SQL Reference
Data Definition

Character Conversion

Introduction

Character Conversion

Character expression conversion is performed on character type data producing either a numeric type data result or a character type data result.

Teradata RDBMS for UNIX SQL Reference

5-91
Data Definition

Character to Numeric

Introduction

Character to Numeric

The form for converting character type data to a numeric data type is:

- CAST— (character_expression — AS — numeric_data_definition) - character_expression— (numeric_data_definition)-----------------

HH01A087

or

— character_expression— (numeric_data_definition)

HH01A088

Before any processing begins, the system scans the numeric description for a FORMAT phrase with an implied decimal point (V). If a V is found, the number of digits is counted after the V and the number of implied decimal positions is saved.

Conversion is performed positionally, character by character, from left to right, until the end of the number.

The following list shows the steps for converting character type data to numeric. Conversion is performed step by step, without returning to a previous step; however, steps may be skipped.

Step Action
1 Leading blanks are ignored except when conversion involves signed zoned decimal input. In this case, an error would result (embedded blanks result in an error in all cases)
2 The sign (+ or -) is saved as part of the number.
3 The currency sign is ignored. Only one currency sign is allowed.
4 Digits are saved as the whole part of the number. Commas, slashes, colons, and percent signs are ignored. The allowance of dollar signs, commas, colons, slashes, percent signs and embedded dashes is a non-ANSI extension of character to numeric conversion.
5 Embedded dashes are allowed, unless the number is signed.
6 The decimal point is saved as the beginning of the fractional part of the number.
7 Digits are saved as the fractional part of the number.
8 The letter E is saved as the beginning of the exponent part of the number. One space is allowed following an E.

5-92

Teradata RDBMS for UNIX SQL Reference
Data Definition

Character to Numeric

Numeric Overflow

FORMAT Phrase Only Controls Resultant Display

Step Action
9 The exponent sign (+ or -) is saved
10 The exponent digits are saved. A sign character cannot appear after any exponent digit.

As noted earlier, in Field Mode, numeric over flow is not treated as an error, but asterisks are displayed in lieu of an unrepresentable number.

In Record and Indicator Variable Modes, numeric overflow is reported as an error. This behavior applies to both the CAST and data definition phrase syntax.

A FORMAT phrase, by itself, cannot be used to convert a character type value to a numeric type value. The phrase only controls how the resultant value is displayed. Some examples of character to numeric conversion are:
Previous << 1 .. 53 54 55 56 57 58 < 59 > 60 61 62 63 64 65 .. 241 >> Next