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 .. 54 55 56 57 58 59 < 60 > 61 62 63 64 65 66 .. 241 >> Next


Character String Converted To Result
'$20,000.00' DECIMAL 20000.00
'$$$.50' DECIMAL error
'$.50' DECIMAL .50
'.345' DECIMAL(8 3) .345
'-1.234E-02' FLOAT -.01234
-2 E. 1 FLOAT error
5 5 INTEGER error
'E67' FLOAT error
'9876' DECIMAL, FORMAT '99V99' 6 7 8 9
'-123' INTEGER -123
'9876' DECIMAL, FORMAT '9(2)V9(2)' 6 7 8 9
3 2 1 INTEGER -123
'123-' INTEGER -123
'-1.234E 02' FLOAT -123.4

Teradata RDBMS for UNIX SQL Reference

5-93
Data Definition

Character To Character

Character To Character

Introduction

The data type of a source character string can be converted by specifying the data type for the target field.

Form

The form for such character data type conversions is: -CAST— (character_expression—AS — character_data_defmition) -------------character_expression — (character_data_defnition)-----------

HH01A083

Usage Notes

If the source string (either CHAR or VARCHAR) is longer than the target data type (CHAR or VARCHAR), excess characters are truncated.

If the session doing an INSERT or UPDATE is in ANSI mode, and non-blank characters would be truncated to store character values in a table, an error is reported.

In Teradata mode, no error is reported.

IF the source string data type is . . . AND it is . . . AND the target data type is . . . THEN . . .
CHAR longer than the target VARCHAR any trailing blanks are trimmed.
CHAR or VARCHAR shorter than the target CHAR any trailing blanks are appended to the target.
CHAR all blanks VARCHAR the field is truncated to zero length.

Examples

Following are examples of character to character conversions:

Character

String

'HELLO' 'HELLO' 'HELLO' 'HELLO ' 'HELLO '

String

Length

5

5

5

7

7

Character

Description

CHAR(3)

CHAR(7)

VARCHAR(7)

VARCHAR(6)

VARCHAR(3)

Conversion

Result

'HEL' 'HELLO ' 'HELLO' 'HELLO ' 'HEL'

Converted

Length

3

7

5

6 3

If CAST is used to convert data to a character string and non-blank characters would be truncated, an error is reported.

5-94

Teradata RDBMS for UNIX SQL Reference
Data Definition

Graphic Conversion

Graphic Conversion

The form for graphic conversion is:

CAST— (graphic_expression — AS — graphic_data_defnition) -----------graphic_expression— (graphic_data_definition)--------

HH01A095

If the length specified by graphic_data_definition is less than the length of graphic_expression or graphic_string, graphic characters beyond the specified length are truncated.

If graphic_data_definition is fixed-length and the length is greater than that of graphic_string, bytes of value double byte zero are appended as required.

For graphic data, the Teradata RDBMS allows conversions only to an identical type (graphic to graphic). Graphic data cannot be converted to any of the following data types:

• numeric

• byte

• character

If CAST is used and significant characters are truncated (or for bytes, if non-zero bytes are truncated), an error is reported.

Teradata RDBMS for UNIX SQL Reference

5-95
Data Definition

Byte Conversion

Byte Conversion

The form for byte conversion is:

—I— CAST — (byte_expression — AS — byte_data_defnition) ——

-----------byte_expression— (byte_data_definition)-------

HH01A096

If the length specified by byte_data_definition is less than the length of byte_expression or byte_string, bytes beyond the specified length are truncated.

If byte_data_definition is fixed-length and the length is greater than that of byte_expression, bytes of value binary zero are appended as required.

For byte data, the Teradata RDBMS allows conversions only to an identical type (byte to byte). Byte data cannot be converted to numeric, graphic, or character data.

If CAST is used and non-zero bytes are truncated, an error is reported.

5-96

Teradata RDBMS for UNIX SQL Reference
Data Definition

DATE Conversion

DATE Conversion

DATE data may be converted to INTEGER, DECIMAL(n,m), or 1OtrOductiOn FLOAT. However, when date data is converted to a numeric, the

value returned is YYMMDD (or YYYYMMDD in a numeric format.

Note: No error is generated on conversion of DATE to DECIMAL or FLOAT.

Date data may also be converted to and from character data. A format phrase may be used when converting character data to date data if the specified format matches the input data. When converting date data to a character string, data is returned as specified in the format phrase.

If CHARACTER is specified, and the number of digits (n) is too small, characters are truncated.

To list employees who were born between March 7, 1938, and Example 1 August 25, 1942, you could specify the date information as follows:

SELECT name, dob FROM employee WHERE dob BETWEEN 3 8 03 07 AND '082542'(DATE, FORMAT 'mmddyy')

ORDER BY dob;

In this example, the first date string is integer in the form yymmdd and requires no qualification. The second string is in the form mmddyy and must be identified to the system. The result returns the date of birth information as specified for the Employee table:
Previous << 1 .. 54 55 56 57 58 59 < 60 > 61 62 63 64 65 66 .. 241 >> Next