Character-to-Numeric Conversion - Teradata Database

Character-to-Numeric Conversion

Purpose

Converts a character data string to a numeric value.

CAST Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a numeric type.
numeric_data_definition	the numeric type to which character_expression is to be converted.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE

ANSI Compliance

This is ANSI SQL:2011 compliant.

As an extension to ANSI, CAST permits the use of data attributes, such as the FORMAT phrase that enables alternative formatting for the numeric data.

Teradata Conversion Syntax

where:

Syntax element …	Specifies …
character_expression	a character expression to be cast to a numeric type.
data_attribute	one of the following optional data attributes: FORMAT NAMED TITLE
numeric_data_type	the numeric type to which character_expression is to be converted.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Implicit Character-to-Numeric Conversion

Implicit character to numeric conversion produces a valid result only if the character string represents a numeric value.

If a CHAR or VARCHAR character string is present in an expression that requires a numeric operand, it is read as a formatted numeric and is converted to a FLOAT value, using the default format for FLOAT.

To override the implicit format, use a FORMAT phrase.

Or, to change the default format for FLOAT, you can change the setting of the REAL element in the specification for data formatting (SDF) file. For information on default data type formats, the SDF file, and the FORMAT phrase, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

To use a CLOB type in an expression that requires a numeric operand, you must first explicitly convert the CLOB to CHAR or VARCHAR.

An empty character string (zero length) or a character string consisting only of pad characters is interpreted as having a numeric value of zero.

If the default format for FLOAT is -9.99E-99, then:

THIS expression …	IS converted to …	AND the result is …
1.1*’$20.00’	1.10E 00*2.00E1	2.20E 01
’2’+’2’	2.00E 00+2.00E 00	4.00E 00
’A’ + 2	----------	error

If a column or parameter of numeric data type is specified with a string value, the string is again assumed to be a formatted numeric. For example, the following INSERT statement specifies the Salary as a numeric string:

   INSERT INTO Employee (EmpNo, Name, Salary) 
   VALUES (10022, 'Clements D', '$38,000.00'); 

The conversion to numeric type removes editing symbols. When selected, the salary data contains only the special characters allowed by the FORMAT phrase for Salary in the CREATE TABLE statement. If the FORMAT phrase is ‘G-(9)D9(2)’, then the output looks like this:

      Salary
   ---------
   38,000.00

If the FORMAT phrase is ‘G-L(9)D9(2)’, then the output looks like this:

       Salary
   ----------
   $38,000.00

Supported Character Types

The character expression to be converted must be CHAR or VARCHAR. CLOBs cannot be explicitly converted to numeric types.

Usage Notes

Before processing begins, the numeric description is scanned for a FORMAT phrase, which is used to determine the radix separator, group separator, currency sign or string, signzone (S), or implied decimal point (V) formatting.

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

Only all-numeric character strings can be converted from character to numeric formats. For example, you can convert the character strings ‘US Dollars 123456’ or ‘123456’ to the integer value 123456, but you cannot convert the string ‘EX1AM2PL3E’ to a numeric value.

The following list shows the steps for converting character type data to numeric. Note that you cannot convert a character_expression of GRAPHIC character type to numeric.

Conversion is performed stage by stage, without returning to a previous stage; however, stages can be skipped.

1 Leading pad characters are ignored. Trailing pad characters are ignored, except for signed zoned decimal input.

Embedded spaces are only allowed according to the following rules:

If the current SDF file defines the group separator as a space, then the character string can include spaces to separate groups of digits to the left of the radix separator, according to the grouping rule defined by GroupingRule or CurrencyGroupingRule.

If the current SDF file defines the radix separator as a space, then the character string can include one space as the radix character.

If the FORMAT phrase contains a currency formatting character, such as N, and the matching currency string in the SDF file, such as CurrencyName, contains a space, the character string can include spaces as part of that currency string.

2 The sign (+ or -) is saved as part of the number. A mantissa sign may appear before the first digit in the string, or after the last digit in the string. An exponent sign may appear with a preceding mantissa sign.

3 The currency sign is ignored if it matches the FORMAT. A currency string is ignored if it matches the FORMAT. Only one currency is allowed in the string.

4 Digits are saved as the integral, fractional, or exponent part of the number, depending on whether the radix or the letter E has been parsed.

5 Separators are ignored, unless they match the radix specified in the FORMAT.

If a separator matches the radix specified in the FORMAT, the location is saved as the beginning of the fractional part of the number. V marks the fractional component for implied decimals.

The allowance of currency and separators is a non-ANSI Teradata extension of character to numeric conversion.

6 Embedded dashes (between digits) are allowed, unless the number is signed or includes a radix, currency, or exponent.

7 The letter E is saved as the beginning of the exponent part of the number. One space is allowed following an E.

8 The exponent sign (+ or -) is saved.

9 The exponent digits are saved. A sign character cannot appear after any exponent digit.

Numeric Overflow

In Field Mode, numeric overflow in character to numeric conversion is not treated as an error. If the result exceeds the number of digits normally reserved for the data type, asterisks are displayed.

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

FORMAT Phrase Controls Parsing of the Data

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)	error1	error
'$.50'	DECIMAL(8,2)	.50	.50
'.345'	DECIMAL(8,3)	.345	.345
'-1.234E-02'	FLOAT	-.01234	-.01234
'-1E.-2'	FLOAT	error2	error
'00000000-.93'	DECIMAL(12,4)	error3	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.

For more information on default data type formats, the SDF file, and the meaning of formatting characters in a FORMAT phrase, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Example : Implicit Conversion of Character to Numeric

The INSERT statement in the following example implicitly converts the character data type to the target numeric data type:

   CREATE TABLE t1 

   (f1 DECIMAL(10,2) FORMAT 'G-U(9)D9(2)');

   INSERT t1 ('USD12,345,678.90');

If a column definition in a CREATE TABLE statement does not specify a FORMAT phrase for the data type, the column uses the corresponding data type default format as defined in the specification for data formatting (SDF) file. For more information on the default format of data types and the definition of formatting characters in a FORMAT phrase, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Related Topics

For details on data types and data attributes, see SQL Data Types and Literals.