Scalar Functions
The scalar functions require either one or two single-value arguments. Scalar functions can be nested to any level.
Note: If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;
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
CAST Expression).
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, INTEGER4, 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. The optional length argument specifies the length of the result in the range 1 to 32000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. 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. The optional length argument specifies the length of the result in the range 1 to 32000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, it is padded using space characters.
Caution! Results may differ from your expectations. For example, SELECT CHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and CHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with CHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT CHAR(0x41). Or use the CHR() function instead of CHAR.
DATE
DATE(expr)
Operand type: C, TEXT, CHAR, VARCHAR, NCHAR, NVARCHAR, INGRESDATE, ANSIDATE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECONDS and TIMESTAMP WITH / WITHOUT TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE and TIME WITH/WITHOUT TIME ZONE
Result type: INGRESDATE, ANSIDATE. The result type depends on the DATE_ALIAS setting.
INGRESDATE, ANSIDATE and TIMESTAMP WITH TIME ZONE. The result type depends on the DATE_ALIAS setting and whether the input parameter is 'NOW'.
Executes ingresdate( ) or ansidate( ) based on the date_alias setting and behaves in a manner similar to the functions accordingly with an exception. When DATE_ALIAS is set to ANSIDATE, DATE('NOW') returns CURRENT_TIMESTAMP (TIMESTAMP WITH TIME ZONE).
INGRESDATE
INGRESDATE(expr)
Operand type: C, TEXT, CHAR, VARCHAR, NCHAR, NVARCHAR, INGRESDATE, ANSIDATE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECONDS and TIMESTAMP WITH / WITHOUT TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE and TIME WITH/WITHOUT TIME ZONE
Result type: INGRESDATE
Converts the expression to internal INGRESDATE representation.
DECIMAL or NUMERIC
DECIMAL(expr [,precision[,scale]])
or
NUMERIC(expr [,precision[,scale]])
Operand type: Any except ingresdate and ANSI datetime
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, INTEGER4
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, INTEGER4
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 Actian X 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, INTEGER4, BOOLEAN
Result type: INTEGER1
Converts the expression to INTEGER1. 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, INTEGER4, BOOLEAN
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
INT(expr)
or
INTEGER(expr)
Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4, BOOLEAN
Result type: INTEGER4
Converts the expression to INTEGER4. 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, INTEGER4, BOOLEAN
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 [, len])
Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE
Result type: LONG VARCHAR
Converts the expression to LONG VARCHAR. The optional length argument specifies the length of the result in the range 0 to 32000 bytes. Values outside the range are adjusted to the minimum or maximum, accordingly.
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, INTEGER4
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. The optional length argument specifies the length of the result in the range 1 to 16000 bytes. Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, it is padded using space characters.
Caution! Results may differ from your expectations. For example, SELECT NCHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and NCHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with NCHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT NCHAR(0x41). Or use the CHR() function instead.
NVARCHAR
NVARCHAR(expr [, len])
Operand type: Any
Result type: NVARCHAR
Converts argument to NVARCHAR Unicode string. The optional length argument specifies the length of the result in the range 1 to 16000 bytes. Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr.
Caution! Results may differ from your expectations. For example, SELECT NVARCHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and NVARCHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with NVARCHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT NVARCHAR(0x41). Or use the CHR() function instead.
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. The optional length argument specifies the length of the result in the range 1 to 32,000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. 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.
TO_CHAR
TO_CHAR (datetime [,format])
Operand type: ANSIDATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE
Result type: VARCHAR
Converts a datetime or interval value to a value of VARCHAR data type in the specified date format. If you omit format, then date is converted to a VARCHAR value as follows:
• ANSIDATE values are converted to values in the default date format.
• TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
• TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
The format can be any of the following：
For TO_CHAR, the case and punctuation of the format are important. Formats whose results are alphabetic follow the case of the format. For example, 'DY' will yield 'MON' but 'Dy' will yield 'Mon'.
SELECT TO_CHAR('2013-07-30 23:42:00.290533-07:00','YYYY-MONTH-DAY HH24:MI:SSXFF6 tzh:tzm');
returns 2013-JULY-TUESDAY 23:42:00.290533 -07:00
SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD');
returns 2013-JUL-30
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
returns 2013-07-30 23:36:19
TO_DATE
TO_DATE(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: ANSIDATE
Converts char to a value of ANSIDATE data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default date format. Valid values for format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added.
The following examples converts a character string into a date:
SELECT TO_DATE('2012-Dec-17', 'YYYY-MON-DD'); returns 2012-12-17.
SELECT TO_DATE('20121217', 'YYYYMMDD'); returns 2012-12-17.
SELECT TO_DATE('2012-Dec', 'YYYY-MON'); returns 2012-12-01.
SELECT TO_DATE('2012', 'YYYY'); returns 2012-01-01.
TO_TIME
TO_TIME_(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: TIME
Converts char to a value of TIME data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default timestamp format. Valid values for
format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.
The following example converts a character string into a timestamp:
SELECT TO_TIME('10:01:59','HH24:MM:SS');
returns 10:01:59
TO_TIMESTAMP
TO_TIMESTAMP(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: TIMESTAMP
Converts char to a value of TIMESTAMP data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default timestamp format. Valid values for
format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.
The following example converts a character string into a timestamp:
SELECT TO_TIMESTAMP('2012-Dec-17', 'YYYY-MON-DD');
returns 2012-12-17 00:00:00.000000
SELECT TO_TIMESTAMP('20121217121314', 'YYYYMMDDHH24MISS');
returns 2012-12-17 12:13:14.000000
SELECT TO_TIMESTAMP ('2012-Dec', 'YYYY-MON');
returns 2012-12-01 00:00:00.000000
SELECT TO_TIMESTAMP ('2012', 'YYYY');
returns 2012-01-01 00:00:00.000000
SELECT TO_TIMESTAMP('2013-10-11 20:01:59.123456','YYYY-MM-DD HH24:MI:SSXFF6');
returns 2013-10-11 20:01:59.123456
TO_TIMESTAMP_TZ
TO_TIMESTAMP_TZ(char [,format])
Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR
Result type: TIMESTAMP WITH TIME ZONE
Converts char to a value of TIMESTAMP data type.
The
format specifies the datetime format of
char. When you omit
format, then
char must be in the default timestamp format. Valid values for
format are shown under
TO_CHAR. The case and punctuation of the format are not important.
If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.
The following example converts a character string into a timestamp:
SELECT TO_TIMESTAMP_TZ('2012-Dec-17', 'YYYY-MON-DD TZH:MZM');
returns 2012-12-17 00:00:00.000000-08:00
SELECT TO_TIMESTAMP_TZ('2013-10-11 20:01:59.123456-04:00','YYYY-MM-DD HH24:MI:SSXFF6 tzh:tzm');
returns 2013-10-11 20:01:59.123456-04:00
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. The optional length argument specifies the length of the result in the range 1 to 32,000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.
Caution! Results may differ from your expectations. For example, SELECT VARCHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and VARCHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with VARCHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT VARCHAR(0x41). Or use the CHR() function instead.
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: