TEXT_TO_INT_ALT - Amazon Redshift
SyntaxArgumentsReturn typeExamples

TEXT_TO_INT_ALT

TEXT_TO_INT_ALT converts a character string to an integer using Teradata-style formatting. Fraction digits in the result are truncated.

Syntax

TEXT_TO_INT_ALT (expression [ , 'format'])

Arguments

expression

An expression that results in one or more CHAR or VARCHAR values, such as a column name or literal string. Converting null values returns nulls. The function converts blank or empty strings to 0.

format

A string literal that defines the format of the input expression. For more information about the formatting characters you can specify, see Teradata-style formatting characters for numeric data.

Return type

TEXT_TO_INT_ALT returns an INTEGER value.

The fractional portion of the cast result is truncated.

Amazon Redshift returns an error if the conversion to the format phrase that you specify isn't successful.

Examples

The following example converts the input expression string '123-' to the integer -123.

select text_to_int_alt('123-');
text_to_int_alt
----------
      -123

The following example converts the input expression string '2147483647+' to the integer 2147483647.

select text_to_int_alt('2147483647+');
text_to_int_alt
----------
2147483647

The following example converts the exponential input expression string '-123E-2' to the integer -1.

select text_to_int_alt('-123E-2');
text_to_int_alt
----------
        -1

The following example converts the input expression string '2147483647+' to the integer 2147483647.

select text_to_int_alt('2147483647+');
text_to_int_alt
----------
2147483647

The following example converts the input expression string '123{' with the format phrase '999S' to the integer 1230. The S character indicates a Signed Zoned Decimal. For more information, see Teradata-style formatting characters for numeric data.

text_to_int_alt('123{', '999S');
text_to_int_alt
----------
      1230

The following example converts the input expression string 'USD123' with the format phrase 'C9(I)' to the integer 123. See Teradata-style formatting characters for numeric data.

text_to_int_alt('USD123', 'C9(I)');
text_to_int_alt
----------
       123

The following example specifies a table column as the input expression.

select text_to_int_alt(a), text_to_int_alt(b) from t_text2int order by 1;
 text_to_int_alt | text_to_int_alt
-----------------+-----------------
            -123 |            -123
            -123 |            -123
             123 |             123
             123 |             123

Following is the table definition and the insert statement for this example.

create table t_text2int (a varchar(200), b char(200));
insert into t_text2int VALUES('123', '123'),('123.123', '123.123'), ('-123', '-123'), ('123-', '123-');