15.00 - Numeric-to-Character Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Numeric-to-Character Conversion

Purpose

Converts a numeric data type to a character data type.

CAST Syntax

where:

 

Syntax element …

Specifies …

numeric_expression

the numeric data expression to be cast to a character type.

character_data_type

the character type to which the numeric data expression is to be converted.

data_attribute

one of the following optional data attributes:

  • CHARACTER SET
  • FORMAT
  • NAMED
  • TITLE
  • If no CHARACTER SET clause is specified to indicate which server character set to use, the user default server character set is used.

    ANSI Compliance

    This is ANSI SQL:2011 compliant.

    As an extension to ANSI, CAST permits the use of data attribute phrases such as FORMAT.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    numeric_expression

    the numeric data expression to be cast to a character type.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • character_data_type

    the character type to which the numeric data expression is to be converted.

    If character_data_definition does not specify a CHARACTER SET clause to indicate which server character set to use, the user default server character set is used.

    server_character_set

    which server character set to use.

    If the CHARACTER SET clause is not specified, the user default server character set is used.

    ANSI Compliance

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

    Implicit Numeric-to-Character Conversion

    If a numeric argument in an SQL string function is implicitly converted to a CHAR or VARCHAR character type, and the format of the numeric argument includes any of the following formatting characters, the server character set of the character type is UNICODE:

     

    For all other formats, the server character set is LATIN.

    Numeric items cannot be converted to CLOB types or GRAPHIC characters.

    For information on data type formats, formatting characters, and the FORMAT phrase, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    How CAST Differs from Teradata Conversion Syntax

    The process for the CAST function is as follows:

    1 Convert the numeric value to a character string using the default or specified format for the numeric value.

    2 Trim leading and trailing pad characters.

    3 Extend to the right as required by the target string length.

    4 If truncation of non-pad characters is required to conform to the target string length, report string truncation error.

    The CAST operation differs from the Teradata SQL conversion as follows:

  • Results are left justified. Column displays are not aligned.
  • Truncation of significant data generates a string truncation error.
  • Using Teradata conversion syntax (that is, not using CAST) for explicit conversion of numeric-to-character data requires caution.

    The process is as follows:

    1 Convert the numeric value to a character string using the default or specified FORMAT for the numeric value.

    Leading and trailing pad characters are not trimmed.

    2 Extend to the right with pad characters if required, or truncate from the right if required, to conform to the target length specification.

    If non-pad characters are truncated, no string truncation error is reported.

    For an example of numeric to character conversion that results in truncation of significant data, see “Example 1” on page 667.

    Supported Character Types

    Numeric to character conversion is supported for CHAR and VARCHAR types only. Numeric types cannot be converted to CLOB types.

    Usage Notes

    To convert a numeric type value 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 to display the resultant value.

    If the character description does not include a FORMAT phrase, then the format of the original numeric value determines how to display the data.

    The Teradata conversion syntax form of numeric-to-character conversion uses explicit or default FORMATs to convert to a character representation. It then truncates or extends with pad characters, depending what length the character string dictates. This can lead to a loss of significance.

    Attempting to convert from a numeric type to a character type that uses a GRAPHIC server character set generates an error.

    As a general rule, you should store numbers as numeric data, not as character data. For example, a table is created with the following code:

       CREATE TABLE job AS
          (job_code CHAR(6) PRIMARY KEY
          ,description CHAR(70) );
     

    Subsequently, the following query is made:

       SELECT job_code, description
       FROM job
       WHERE job_code = 1234;

    The problem here is that ‘1234’, ‘1234’, ‘01234’, ‘001234’, ‘+1234’, and so on, are all valid character representations of the numeric literal value, and the system cannot tell which value to use for hashing. Therefore, the system must do a full table scan to convert all job_code values to their numeric equivalents so that it can do the comparisons.

    Example  

    T1.Field1 has a numeric INTEGER data type with the default format ‘-(10)9’. The user has values such as 123456, with no values of over 999999. The values, defined as being in INTEGER format, are to be converted to CHAR(8).

    The following example illustrates the Teradata syntax for performing this numeric-to-character conversion.

       SELECT Field1(CHAR(8)) FROM T1;

    returns ‘      123’ for the value 123456, where the result includes 5 leading pad characters and truncates significant digits.

    Example  

    Based on the following description of Salary, data is converted as illustrated in the following table (Δ = pad character):

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

    Data

    Conversion

    Result

    20000.00

    Salary (CHAR(10))

    '$20,000.00'

    9000.00

    Salary (CHAR(10))

    'Δ$9,000.00'

    20000.00

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

    '20000'

    9000.00

    CAST (Salary AS CHAR(10))

    '$9,000.00Δ'

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

    Example  

    Suppose EmpNo was defined as SMALLINT with the default 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 pad character 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 pad characters, and finally truncates or pads on the right. For example, the following SELECT statement returns ‘12501’.

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

    Related Topics

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