FORMAT Phrase Controls Parsing of the Data

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

A FORMAT phrase, by itself, cannot convert a character type value to a numeric type value. The phrase controls partially how the resultant value is parsed.

Some examples of character to numeric conversion appear in the following table. For FORMAT phrases that contain G, D, C, and N formatting characters, assume that the related entries in the specification for data formatting file (SDF) are:

RadixSeparator {"."}
GroupSeparator {","}
GroupingRule   {"3"}
Currency       {"$"}
ISOCurrency    {"USD"}
CurrencyName   {"US Dollars"}
Character String Converted To Resultant Numeric Value Field Mode Display Result
'$20,000.00' DECIMAL(10,2) 20000.00 20000.00
'$$$.50' DECIMAL(10,2) error

Only one currency is allowed in the character string.

error
'$.50' DECIMAL(8,2) .50 .50
'.345' DECIMAL(8,3) .345 .345
'-1.234E-02' FLOAT -.01234 -.01234
'-1E.-2' FLOAT error

The radix must precede the exponent part of the number.

error
'00000000-.93' DECIMAL(12,4) error

Embedded dashes cannot appear in a string containing a radix.

error
'-  55' INTEGER -55 -55
'E67' FLOAT 0.0 0.00000000000000E 000
'9876' DECIMAL(4,2)

FORMAT '99V99'

98.76 9876
'-123' INTEGER -123 -123
'9876' DECIMAL(4,2)

FORMAT '9(2)V9(2)'

98.76 9876
'1-2-3' INTEGER 123 123
'123-' INTEGER -123 -123
'123-  ' INTEGER -123 -123
'-1.234E 02' FLOAT -123.4 -1.23400000000000E 002
'111,222,333' INTEGER

FORMAT 'G9(I)'

111222333 0,111,222,333
'2.49US Dollars' DECIMAL(10,2)

FORMAT 'GZ(I)D9(F)BN'

2.49 2.49 US Dollars
'25000USD' INTEGER

FORMAT '9(I)C'

25000 0000025000USD

A conversion that does not specify a FORMAT phrase uses the corresponding data type default format as defined in the SDF.