Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.6
- 10.5.9
- 10.5.8
- 10.5.7
- 10.5.4
- 10.5.1
- 10.5
- 10.4.0

Back Next

TO_INTEGER

Converts a string or numeric value to an integer. TO_INTEGER syntax contains an optional argument that you can choose to round the number to the nearest integer or truncate the decimal portion. TO_INTEGER ignores leading blanks.

Syntax

TO_INTEGER( value [, flag] )

The following table describes the arguments for this command:

Argument	Required/ Optional	Description
value	Required	String or numeric datatype. Passes the value you want to convert to an integer. You can enter any valid transformation expression.
flag	Optional	Specifies whether to truncate or round the decimal portion. The flag must be an integer literal or the constants TRUE or FALSE. TO_INTEGER truncates the decimal portion when the flag is TRUE or a number other than 0. TO_INTEGER rounds the value to the nearest integer if the flag is FALSE or 0 or if you omit this argument.

Return Value

Integer.

NULL if the value passed to the function is NULL.

If the value passed to the function contains data that is not valid for an integer value, the Data Integration Service marks the row as an error row or fails the mapping.

Examples

The following expressions use values from the port IN_TAX. The

Data Integration Service

displays an error when the conversion causes a numeric overflow:

TO_INTEGER( IN_TAX, TRUE )

IN_TAX	RETURN VALUE
'15.6789'	15
'60.2'	60
'118.348'	118
'5,000,000,000'	Error. Integration Service skips this row.
NULL	NULL
'A12.3Grove'	Error. Integration Service skips this row.
' 123.87'	123
'-15.6789'	-15
'-15.23'	-15

TO_INTEGER( IN_TAX, FALSE)

IN_TAX	RETURN VALUE
'15.6789'	16
'60.2'	60
'118.348'	118
'5,000,000,000'	Error. Integration Service skips this row.
NULL	NULL
'A12.3Grove'	Error. Integration Service skips this row.
' 123.87'	124
'-15.6789'	-16
'-15.23'	-15

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

TO_INTEGER

TO_INTEGER

Syntax

Return Value

Examples