Conversion Functions
Conversion functions convert the expression from one data type into another type. Type conversions can also be specified using the
CAST expression (see page
CAST Expressions).
The following data type conversion functions are supported:
ANSIDATE
ANSIDATE(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, ANSIDATE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: ANSIDATE
Converts the expression to internal ANSIDATE representation.
BOOLEAN
BOOLEAN(expr)
Operand type: CHAR, VARCHAR, C, TEXT
Result type: BOOLEAN
Converts the string 'FALSE' and 'TRUE', without regard to case and where applicable, trailing or internal whitespace, to the corresponding BOOLEAN values.
Operand type: INTEGER1, SMALLINT, INTEGER, BIGINT
Result type: BOOLEAN
Converts the value 0 to FALSE and the value 1 to TRUE.
BYTE or BINARY
BYTE(expr [, len])
or
BINARY(expr [, len])
Operand type: Any
Result type: BYTE
Converts the expression to BYTE binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using null bytes.
Note: Leftmost refers to the leftmost byte in memory. This can return a different result for the same variable value depending on whether the machine is big or little endian.
Note: The binary data depends on the data type selected during the coercion of the literal in expr. This may not be the data type the user expects. For example, a very large integer literal that is too large to be stored as an INTEGER8 will be coerced into a FLOAT.
C
C(expr [, len])
Operand type: Any except long data types
Result type: C
Converts argument to C string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.
CHAR
CHAR(expr [, len])
Operand type: Any
Result type: CHAR
Converts argument to char string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.
DATE or INGRESDATE
DATE(expr)
or
INGRESDATE(expr)
Operand type: C, TEXT, CHAR, VARCHAR, NCHAR, NVARCHAR
Result type: INGRESDATE
Converts a C, CHAR, VARCHAR, TEXT, NCHAR, or NVARCHAR string to internal date representation.
DECIMAL or NUMERIC
DECIMAL(expr [,precision[,scale]])
or
NUMERIC(expr [,precision[,scale]])
Operand type: Any except ingresdate and ANSI date/time
Result type: DECIMAL
Converts any numeric expression to a decimal value. If scale (number of decimal digits) is omitted, the scale of the result is 0. If precision (total number of digits) is omitted, the precision of the result is determined by the data type of the operand, as follows:
Decimal overflow occurs if the result contains more digits to the left of the decimal point than the specified or default precision and scale can accommodate.
DOW
DOW(expr)
Operand type: Any absolute date
Result type: C
Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.
FLOAT4
FLOAT4(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL, INTEGER1, SMALLINT, INTEGER
Result type: FLOAT4
Converts the specified expression to FLOAT4. Numeric overflow can occur if the argument is too large for the result type (possible from string or decimal).
The range of values for float4 is processor dependent.
FLOAT8
FLOAT8(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
Result type: FLOAT
Converts the specified expression to FLOAT. Numeric overflow can occur if the argument is too large for a float (possible from string or decimal).
The range of values for float is determined by IEEE_754.
HEX
HEX(expr)
Operand type: Any
Result type: VARCHAR
Returns the hexadecimal representation of the internal Ingres form of the argument expression. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each byte of the argument requires two bytes. For example, hex('ABC') returns '414243' (ASCII) or 'C1C2C3' (EBCDIC). Also, hex(int4(125)) returns '0000007D', the hexadecimal equivalent of the 4 byte binary integer 125.
INT1 or TINYINT
INT1(expr)
or
TINYINT(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
Result type: TINYINT
Converts the expression to TINYINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INT2 or SMALLINT
INT2(expr)
or
SMALLINT(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
Result type: SMALLINT
Converts the expression to SMALLINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INT4 or INT or INTEGER
INT4(expr)
or
IINT(expr)
or
INTEGERr(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGERResult type: INTEGER
Converts the expression to INTEGER. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INT8 or BIGINT
INT8(expr)
or
BIGINT(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
Result type: BIGINT
Converts the expression to BIGINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.
INTERVAL_DTOS
INTERVAL_DTOS(expr)
Operand type: C, CHAR, VARCHAR, TEXT, INGRESDATE, INTERVAL DAY TO SECOND
Result type: INTERVAL DAY TO SECOND
Converts the expression to internal INTERVAL DAY TO SECOND representation.
INTERVAL_YTOM
INTERVAL_YTOM(expr)
Operand type: C, CHAR, VARCHAR, TEXT, INGRESDATE, INTERVAL YEAR TO MONTH
Result type: INTERVAL YEAR TO MONTH
Converts the expression to internal INTERVAL YEAR TO MONTH representation.
LONG_BYTE or LONG_BINARY
LONG_BYTE(expr)
or
LONG_BINARY(expr)
Operand type: Any
Result type: LONG BYTE
Converts the expression to LONG BYTE binary data.
LONG_VARCHAR
LONG_VARCHAR(expr)
Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE
Result type: LONG VARCHAR
Converts the expression to LONG VARCHAR.
LONG_NVARCHAR
LONG_NVARCHAR(expr)
Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE
Result type: LONG NVARCHAR
Converts the expression to LONG NVARCHAR.
MONEY
MONEY(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER
Result type: MONEY
Converts the expression to internal MONEY representation. Rounds floating point and decimal values, if necessary.
NCHAR
NCHAR(expr [, len])
Operand type: Any
Result type: NCHAR
Converts argument to NCHAR Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.
NVARCHAR
NVARCHAR(expr [, len])
Operand type: Any
Result type: NVARCHAR
Converts argument to NVARCHAR Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr.
OBJECT_KEY
OBJECT_KEY(expr)
Operand type: VARCHAR, CHAR, C, TEXT
Result type: OBJECT_KEY
Converts the operand to an OBJECT_KEY.
TABLE_KEY
TABLE_KEY(expr)
Operand type: VARCHAR, CHAR, C, TEXT
Result type: TABLE_KEY
Converts the operand to a TABLE_KEY.
TEXT
TEXT(expr [, len])
Operand type: Any
Result type: TEXT
Converts argument to TEXT string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.
TIME_LOCAL
TIME_LOCAL(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIME WITH LOCAL TIME ZONE
Converts the expression to internal TIME WITH LOCAL TIME ZONE representation.
TIME_WITH_TZ
TIME_WITH_TZ(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIME WITH TIME ZONE
Converts the expression to internal TIME WITH TIME ZONE representation.
TIME or TIME_WO_TZ
TIME(expr)
or
TIME_WO_TZ(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIME WITHOUT TIME ZONE
Converts the expression to internal TIME WITHOUT TIME ZONE representation.
TIMESTAMP_LOCAL
TIMESTAMP_LOCAL(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIMESTAMP WITH LOCAL TIME ZONE
Converts the expression to internal TIMESTAMP WITH LOCAL TIME ZONE representation.
TIMESTAMP_WITH_TZ
TIMESTAMP_WITH_TZ(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIMESTAMP WITH TIME ZONE
Converts the expression to internal TIMESTAMP WITH TIME ZONE representation.
TIMESTAMP or TIMESTAMP_WO_TZ
TIMESTAMP(expr)
or
TIMESTAMP_WO_TZ(expr)
Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
Result type: TIMESTAMP WITHOUT TIME ZONE
Converts the expression to internal TIMESTAMP WITHOUT TIME ZONE representation.
UNHEX
UNHEX(expr)
Operand type: Any
Result type: VARCHAR
Returns the opposite of the hex function. For example, unhex('61626320') returns 'abc ' and unhex('01204161') returns '\001Aa'.
Exceptions can occur when a "c" data type suppresses the display of certain stored characters, or when the output data type differs from the input type.
Note: Normally one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value.
VARBYTE or VARBINARY
VARBYTE(expr [, len])
or
VARBINARY(expr [, len])
Operand type: Any
Result type: BYTE VARYING
Converts the expression to BYTE VARYING binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.
VARCHAR
VARCHAR(expr [, len])
Operand type: Any
Result type: VARCHAR
Converts argument to VARCHAR string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.
Length of Results for Data Type Conversions
When converting decimal values to strings, the length of the result depends on the precision and scale of the decimal column.
Some functions have an optional length parameter. If this parameter is omitted, the length of the result returned by the data type conversion functions c(), char(), varchar(), nchar(), nvarchar(), and text() are as follows: