Was this helpful?
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. 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. 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:
 
Operand Data Type
Default Precision
tinyint
5
smallint
5
integer
11
bigint
19
float
15
float4
15
decimal
15
money
15
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 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, 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:
Format
Description
punctuation "text"
Preserves literal punctuation and quoted text
AD
A.D.
AD indicator
AM
A.M.
PM
P.M.
AM/PM indicator
D
Number of day of week 1 - 7
DAY
Day name padded to suitable width to retain column
DD
Day of month 1 - 31
DDD
Day of year 1 - 366
DY
Day name in 3 characters
FF [1..9]
Fractional seconds
FM
Toggle whether formats have leading or trailing blanks
FX
Toggle whether exact matching is required
HH
Hour of day 1 - 12
HH12
Hour of day 1 - 12 (Supported in TO_CHAR only.)
HH24
Hour of day 0 - 23
J
Julian day (days since January 1, 4712 BC)
MI
Minutes 0 - 59
MM
Month 01 - 12
MON
First 3 characters of month name
MONTH
Month name padded to suitable length
RM
Month in Roman numerals
RR
Round year in 2 digits
RRRR
Round year in 2 or 4 digits
SS
Seconds 0 - 59
SSSS
Seconds since midnight 0 - 86399
TZH
Time zone hour
TZM
Time zone minute
X
Decimal
Y,YYY
Year with comma in this position
YYYY
YYY
YY
Y
Last 4, 3, 2, or 1 digits of year
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','YEAR-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.
Note:  If date contains an empty string, it returns the current date.
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:
Data Type of Argument
Result Length
byte
Length of operand
byte varying
Length of operand
c
Length of operand
char
Length of operand
decimal
Depends on precision and scale of column
float & float4
11 characters; 12 characters on IEEE computers
ingresdate
25 characters
integer1 (tinyint)
Maximum 4 characters
integer2 (smallint)
Maximum 6 characters
integer
Maximum 11 characters
integer8 (bigint)
Maximum 19 characters
long byte
Length of operand
long varchar
Length of operand
long nvarchar
2 x length of operand
nchar
2 x length of operand
nvarchar
2 x length of operand
money
20 characters
text
Length of operand
varchar
Length of operand
Numeric Functions
For trigonometric functions (COS, SIN, TAN), specify argument in radians. To convert degrees to radians, use the formula: radians = degrees / 360 * 2 * pi(). The functions ACOS, ASIN, ATAN, AND ATAN2 return a value in radians.
ABS
ABS(n)
Operand type: All numeric types and MONEY
Result type: Same as n
Absolute value of n
ACOS
ACOS(n)
Operand type: All numeric types
Result type: FLOAT
Arccosine of cosine value n
ASIN
ASIN(n)
Operand type: All numeric types
Result type: FLOAT
Arcsine value of sine value n
ATAN
ATAN(n)
Operand type: All numeric types
Result type: FLOAT
Arctangent of n; returns a value from (-pi/2) to pi/2.
ATAN2
ATAN2 (x, y)
Operand type: All numeric types
Result type: FLOAT
Arctangent of angle defined by coordinate pair (x, y)
CEIL
CEIL(n)
CEILING(n)
Operand type: All numeric types and MONEY
Result type: Numeric type, based on operand.
Returns the smallest whole value greater than or equal to the specified numeric expression.
Returns a decimal if input is decimal, and a float if input is float. Coerces other types to decimal or float, according to normal coercion preferences for the input type, and then returns result based on the coerced input.
COS
COS(n)
Operand type: All numeric types
Result type: FLOAT
Cosine of n; returns a value from -1 to 1.
EXP
EXP(n)
Operand type: All numeric types and MONEY
Result type: FLOAT
Exponential of n
FLOOR
FLOOR(n)
Operand type: All numeric types and MONEY
Result type: Numeric type, based on operand.
Returns the largest whole value less than, or equal to, the specified numeric expression.
Returns a decimal if input is decimal, and a float if input is float or money. Coerces other types to decimal or float, according to normal coercion preferences for the input type, and then returns result based on the coerced input.
LOG
LOG(n)
LN(n)
Operand type: All numeric types and MONEY
Result type: FLOAT
Natural logarithm of n
MOD
MOD(n,b)
Operand type: INTEGER1, SMALLINT, INTEGER4, DECIMAL
Result type: Same as b
n modulo b. The result is the same data type as b. Decimal values are truncated.
PI
PI()
Operand type: None
Result type: FLOAT
Value of pi (ratio of the circumference of a circle to its diameter)
POWER
POWER(x,y)
Operand type: All numeric types
Result type: FLOAT
x to the power of y (identical to x ** y)
ROUND
ROUND(n,i)
Operand type: All numeric types
Result type: Type of n.
Rounds value n at the i'th place right or left of the decimal, depending on whether i is greater or less than 0.
WARNING!  When used with a floating point argument, rounding errors may occur.
SIGN
SIGN(n)
Operand type: All numeric types and MONEY
Result type: INTEGER4
Returns -1 if n < 0, 0 if n = 0, +1 if n > 0
SIN
SIN(n)
Operand type: All numeric types
Result type: FLOAT
Sine of n; returns a value from -1 to 1.
SQRT
SQRT(n)
Operand type: All numeric types and MONEY
Result type: FLOAT
Square root of n
TAN
TAN(n)
Operand type: All numeric types
Result type: FLOAT
Tangent value of angle n
TRUNC
TRUNC(x ,y)
TRUNCATE(x ,y)
Operand type: All numeric types
Result type: DECIMAL
Truncates x at the decimal point, or at y places to the right or left of the decimal, depending on whether y is greater or less than 0.
String Functions
String functions perform a variety of operations on character data.
String functions can be nested. For example:
LEFT(RIGHT(x.name, SIZE(x.name) - 1), 3)
returns the substring of x.name from character positions 2 through 4.
The || or + operator can also be used to concatenate strings:
x.lastname || ', ' || x.firstname
Note:  Using + should be avoided. It is an overloaded operator and can result in ambiguity when string columns and literals that contain only numeric values are mixed with numeric columns or literals.
SELECT '1' + '40' + '50' returns 14050
SELECT 1 + '40' + '50' returns 91
SELECT '1' + 40 + '50' returns 91
SELECT '1' + 40 || '50' returns 4150
ASCII
ASCII(v1)
Result type: Any character type
Returns the character equivalent of the value v1, which is an expression of any type.
BYTEEXTRACT
BYTEEXTRACT(c1,n)
Result type: BYTE
Returns the nth byte of c1. If n is larger than the length of the string, the result is a byte of ASCII 0.
Note:  This function does not support LONG VARCHAR, LONG NVARCHAR, or LONG BYTE arguments.
CHARACTER_LENGTH
CHARACTER_LENGTH(c1)
Result type: INTEGER4
Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function.
Note:  This function does not support NCHAR and NVARCHAR arguments.
CHAREXTRACT
CHAREXTRACT(c1,n)
Result type: VARCHAR or NCHAR
Returns the nth character or code point of c1. If n is larger than the length of the string, the result is a blank character.
Note:  For Unicode (USC2) strings the value returned is the single USC2 character. For non-Unicode strings the values returned may be 1,2,3 or 4 bytes long depending on the character at offset n characters of c1.
SELECT CHAREXTRACT('company',4) returns 'p'
CHR
CHR(n)
Result type: CHAR
Converts integer into corresponding ASCII code. If n is greater than 255, the conversion is performed on n mod 256.
SELECT CHR(65) returns 'A'.
SELECT CHR(345) returns 'Y'.
COLLATION_WEIGHT
COLLATION_WEIGHT(c1 [,n1])
Result type: VARBYTE
Returns the collation weight of any char, c, varchar, text, nchar, or nvarchar value c1. n1 is an optional collation ID when the collation weight is desired relative to a specific collation.
CONCAT
CONCAT(c1,c2...)
Result type: Any character or Unicode type, BYTE
Concatenates two or more strings. CONCAT takes a minimum of one operand.
Note:  When used in an Ingres 4GL statement, the function concatenates no more than two strings.
SELECT CONCAT('1', '2', '3', '4') returns '1234'.
In earlier releases, to achieve the same result, you must nest the CONCAT function: SELECT CONCAT(CONCAT(CONCAT('1', '2'), '3'), '4').
The result size is the sum of the sizes of the arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation.
Note:  The concatenation of the BYTE data type cannot be used to create a table column implicitly in Ingres tables; the result of such concatenation, however, can be inserted into a column with a character data type.
Wrong:
CREATE TABLE CONCAT2 AS SELECT CONCAT(BYTE('1'), 0x42, X'43', CAST('4' AS BYTE));
Correct:
CREATE TABLE test_concat (col1 VARCHAR(4);
INSERT INTO test_concat SELECT CONCAT(BYTE('1'), 0x42, X'43', CAST('4' AS BYTE));
DMETAPHONE
DMETAPHONE(c1)
Result type: VARCHAR
Returns the metaphone code from a varchar string (vch1) using the double metaphone algorithm.
Alias: DOUBLE_METAPHONE.
SELECT DMETHAPHONE('XXXXXXX') returns XXXXXXX
GENERATE_DIGIT
GENERATE_DIGIT('scheme', 'c1')
Result type: VARCHAR(2)
Generates the check digit for the string c1 using the specified encoding scheme.
Supported schemes are described under VALIDATE_DIGIT (see VALIDATE_DIGIT).
The check digit is computed from the other characters in the string. Although the majority of check digits are a single character, some encoding schemes may return a two character check digit. Typically the check digit is appended to the string for validation, but this is not the case in all encoding schemes.
Note:  This function does not append the check digit to the string.
The check digit can be used to help determine if a string has been entered correctly. A check digit may be able to detect several types of errors, including:
Mistyped characters ('0123' not '0124')
Omissions or additions of characters
Adjacent transposition errors ('01234' not '01243')
Twin errors ('11' becomes '22')
Jump transpositions (123 becomes 321)
Jump twin errors (121 becomes 323)
Phonetic errors—the mixing of similar sounding numbers (sixty '60' not sixteen '16')
A check digit improves the chance of detecting errors in keying, but is not an infallible error detector. Furthermore, a check digit does not provide error correction capability.
There are many check digit schemes, each with its own purpose, capabilities, and complexity. For example, the Luhn algorithm is widely used in credit card numbers and is programmatically simple to implement. The Verhoeff algorithm, in contrast, is better at detecting certain error types such as jump transpositions, but is more complicated.
The scheme name is case insensitive. When specifying the LUHN or VERHOEFF scheme, the function can take as input a character string of any length. The other schemes are fixed length, so the input string must be of the correct length.
Note:  This function does not support NCHAR and NVARCHAR strings as arguments.
II_IPADDR
II_IPADDR(n)
Result type: VARBYTE
Converts an IPv4 (dotted-decimal) or IPv6 (coloned-hex) address to a varbyte data type of length 4 or 16 respectively. Each number between the periods of an IPv4 address is converted into a single byte in the hex result. For IPv6, each hex number between the colons is converted to two bytes in the hex result. Double-colons and IPv4-mapped IPv6 addresses are supported as well.
Views and database procedures created prior to Ingres 9.2 will continue to exhibit the old behavior until they are recreated. Previously, only IPv4 addresses were supported and the returned value was type=byte with length=4.
For example:
255.255.0.0 produces ‘ffff0000’;
10.20.30.40 produces ‘0a141e28’
2.1 produces “02000001”
fe80::208:74ff:fef0:42b3 produces “FE80000000000000020874FFFEF042B3”
::1 produces “00000000000000000000000000000001”
::FFFF:141.202.36.10 produces “00000000000000000000FFFF8DCA240A”
INITCAP
INITCAP(c1)
Result type: Any character or Unicode type
Converts the first letter of each word in c1 to upper case and the remaining letters in each word to lowercase.
SELECT INITCAP('This is the final version (VERSION:5.a;6) of Leonard''s will')
returns:'This Is The Final Version (Version:5.A;6) Of Leonard's Will'
INSTR
INSTR(c1, c2, n)
Result type: Integer type
Returns the position of the nth occurrence of c2 in c1.
SELECT INSTR('babababa', 'bab', 2) returns 3.
JARO_WINKLER
JARO_WINKLER(str1, str2 [, len])
Result type: FLOAT4
Calculates the Jaro_Winkler similarity between two strings str1 and str2 and returns the float value between 0 and 1. Here, 0.0 means no similarities and 1.0 means the strings are identical. If the optional length parameter is omitted, the strings must be of type VARCHAR and the comparison is limited to the first 60 characters for longer strings.
If the length parameter is specified, the strings can be either VARCHAR or LONG VARCHAR and the comparison will apply to the user specified length. If the specified length is zero or greater than the length of both strings, the full strings are compared.
SELECT JARO_WINKLER('same','same') returns 1.0
SELECT JARO_WINKLER(‘abcd1’, ’abcd2’, 4) returns 1.0
SELECT JARO_WINKLER(‘abcd1’, ’abcd2’, 0) returns 0.920
LEFT
LEFT(c1,len)
Result type: Any byte, character, or Unicode type
Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1. len will be converted to a positive integer.
If len is not an integer value it will be rounded down equivalent to floor(len). If len is negative the result will be an empty string.
SELECT LEFT ('Company',4) returns 'Comp'
LENGTH
LENGTH(c1)
Result type: SMALLINT (for LONG VARCHAR, returns INTEGER4)
If c1 is a fixed-length c or char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1.
LEVENSHTEIN
LEVENSHTEIN(c1,c1)
Result type: INTEGER4
Calculates the Levenshtein distance between two VARCHAR strings. The Levenshtein distance between two strings is the minimum number of changes that need to be made to convert the source string into the target string.
Alias: LEVENSHTEIN_DISTANCE
SELECT LEVENSHTEIN('foo','fou') returns 1
LOCATE
LOCATE(c1,c2)
Result type: SMALLINT
Returns the location of the first occurrence of c2 within c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table.
If c1 and c2 are different string data types, c2 is coerced into the c1 data type.
SELECT LOCATE ('Company', 'p') returns 4
LOWERCASE or LOWER
LOWERCASE(c1)
or
LOWER(c1)
Result type: Any character or Unicode type
Converts all upper case characters in c1 to lower case.
SELECT LOWER ('Company') returns 'company'
LPAD
LPAD(expr1, n [, expr2])
Result type: Any character type
Returns character expression of length n in which expr1 is prepended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the start of the result string.
SELECT LPAD ('Company',20, '-') returns '-------------Company'
LTRIM
LTRIM(expr)
Result type: Any character type
Returns character expression with leading blanks removed.
SELECT LTRIM ('  Company') returns 'Company'
MD5SUM
MD5SUM(c1)
Result type: CHAR(32)
Returns the calculated MD5 message digest (also known as md5sum) of c1. The computed sum generated is a lowercase hexadecimal string.
SELECT MD5SUM('a') returns '0cc175b9c0f1b6a831c399e269772661'.
NOTRIM
NOTRIM(c1)
Result type: Any character string variable
Retains trailing blanks when placing a value in a varchar column. This function can be used only in an embedded SQL program. For more information, see the Embedded SQL Companion Guide.
OCTET_LENGTH or LENGTHB
OCTET_LENGTH(c1[,charset])
or
LENGTHB(c1[,charset])
 
Result type: INTEGER4
Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function.
The result when multi-character datasets are used will be longer as each byte of a multi-bye character is counted.
The second parameter is any valid characterset supported by the database.
CREATE TABLE octet(col1 VARCHAR(10), col2 CHAR(10));
INSERT INTO octet VALUES ('Company', 'Company')
SELECT OCTET_LENGTH (col1) returns 7
SELECT LENGTHB (col1) returns 7
SELECT OCTET_LENGTH (col2) returns 10
SELECT LENGTHB (col2) returns 10
SELECT LENGTH (col2) from octet \g returns 7
Note:  The CLOB (long varchar) and NCLOB (long nvarchar) data types are not supported for the LENGTHB function and OCTET_LENGTH function.
 
PAD
PAD(c1)
Result type: TEXT, VARCHAR, or NVARCHAR
Returns c1 with trailing blanks appended. For example: if c1 is a varchar string that can hold fifty characters but only has two characters, pad(c1) appends 48 trailing blanks to c1 to form the result.
Note:  This function does not support LONG VARCHAR or LONG NVARCHAR arguments.
POSITION
POSITION(c1 IN c2)
POSITION(c1 , c2)
Result type: SMALLINT
ANSI compliant version of LOCATE function. If a match exists, POSITION(c1 IN c2) is equal to LOCATE(c2, c1). If a match does not exist, POSITION returns 0, unlike LOCATE.
SELECT POSITION('p', 'Company') returns 4.
SELECT POSITION('z', 'Company') returns 0.
SELECT LOCATE('Company', 'p') returns 4 (same result as POSITION).
SELECT LOCATE('Company', 'z') returns 8 (one more than LENGTH(‘company’).
REPEAT
REPEAT(c1, n)
Result type: Any character or Unicode type except the long forms
Returns c1 (a character string) repeated n times. REPLICATE is a synonym for REPEAT. The maximum length of the result is 32000 bytes (or 16000 bytes in a UTF8 installation).
Note:  This function does not support LONG VARCHAR and LONG NVARCHAR arguments.
REPEAT ('-',10) returns '----------'
REPEAT ('str',3) returns 'strstrstr'
REPLACE
REPLACE(expr1, expr2, expr3)
Result type: Any byte or character type
Returns character expression derived from expr1 in which all instances of expr2 have been replaced by expr3.
SELECT REPLACE('The priory was in the next town','priory','church')
returns: 'The church was in the next town'
REVERSE
REVERSE(c1)
Result type: CHAR, VARCHAR, NCHAR, NVARCHAR
Returns the string c1 with the in-memory order of the characters reversed, based on code points. For CHAR and NCHAR, the trailing space is also reversed.
Reversal of code points is supported for all string types in both UTF8 and non-UTF8 installations.
In the following examples, each underline character (_) represents a space.
Data Type
Input
Output
CHAR(8)
hello
___olleh
NCHAR(8)
hello
___olleh
VARCHAR(8)
hello
olleh___
NVARCHAR(8)
hello
olleh___
SELECT REVERSE('ecnalubma') returns ambulance.
Example for Unicode input:
SELECT REVERSE('Adán') returns nádA. The string 'Adán' is 4 code points:
U+0041 Latin Capital Letter A
U+0064 Latin Small Letter D
U+00E1 Latin Small Letter A with Acute
U+006E Latin Small Letter N
REVERSE(c1, 'egc')
Result type: CHAR, VARCHAR, NCHAR, NVARCHAR
Returns the string c1 with the in-memory order of the characters reversed, based on characters consisting of multiple code points (extended grapheme cluster). For CHAR and NCHAR, the trailing space is also reversed.
Reversal of extended grapheme clusters is supported for types CHAR and VARCHAR in case the data is interpreted as UTF8 (due to the installed character set or collation). For types NCHAR and NVARCHAR the reversal of extended grapheme clusters is supported independently of the installed character set.
For more information on extended grapheme clusters, see the Unicode Standard Annex #29 “Unicode Text Segmentation” at https://www.unicode.org/reports/tr29.
Example for Unicode input:
SELECT REVERSE('Adán', 'egc') returns nádA. The string 'Adán' is 5 code points:
U+0041 Latin Capital Letter A
U+0064 Latin Small Letter D
U+0061 Latin Small Letter A
U+0301 Combining Acute Accent
U+006E Latin Small Letter N
The a gets acute because U+0061 and U+0301 build a grapheme cluster.
To determine the number of code points in a string, use the LENGTH() function.
RIGHT
RIGHT(c1,len)
Result type: Any byte, character, or Unicode type
Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1. len must be a positive integer.
SELECT RIGHT('The priory was in the next town',9)
returns 'next town'
RPAD
RPAD(expr1, n [, expr2])
Result type: Any character type
Returns character expression of length n in which expr1 is appended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the end of the result string.
SELECT RPAD('Company',12, '-') returns 'Company-----'
SELECT RPAD('Company',12, '-x') returns 'Company-x-x-'
RTRIM
RTRIM(expr)
Result type: Any character type
Returns character expression with trailing blanks removed.
SELECT LENGTH(RTRIM('Company     ')) returns 7
SHIFT
SHIFT(c1,nshift)
Result type: Any character or Unicode type
Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
SELECT SHIFT('Company',4) returns '   Com'
SELECT SHIFT('Company',‑4) returns 'any    '
SIZE
SIZE(c1)
Result type: smallint
Returns the declared size of c1 without removal of trailing blanks.
SELECT SIZE('Company') returns 7
SOUNDEX
SOUNDEX(c1)
Result type: Any character type
Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same SOUNDEX code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digits.
This standard (Russell) soundex function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than just the next strings in the index.
Note:  This function does not support LONG VARCHAR or any Unicode arguments.
SELECT SOUNDEX('Company') returns 'C515'
SELECT SOUNDEX ('Company2012') returns 'C515'
SOUNDEX_DM
SOUNDEX_DM(c1)
Result type: Any character type
Returns one or more six-character codes in a comma-separated varchar string (up to a maximum of 16 codes) that can be used to find similar sounding names.
This Daitch-Mokotoff soundex function returns one or more six-character codes in a comma-separated varchar string (up to a maximum of 16 codes) that can be used to find similar sounding names. For example: the codes 739460,734600 may be returned for the name Peterson.
Leading and embedded spaces are ignored in the input. The input strings are terminated by non-alphabetic characters other than period (.), apostrophe ('), or hyphen (-). The function ignores the first occurrence of any of these characters, but terminates the string on a subsequent occurrence. This allows the input of names or place names with standard punctuation marks. For example:
O'Brien is treated as 'OBRIEN'
St.Kilda is treated as 'STKILDA'
St. Hilda is treated as 'STHILDA'
'Smyth-Brown' is treated as 'SMYTHBROWN'
An empty name, or a name with characters unable to be encoded, returns a code of 000000 and no error is generated.
Note:  This function does not support LONG VARCHAR or any Unicode arguments.
For more information, see SOUNDEX_DM vs. SOUNDEX.
SOUNDEX_NYSIIS
SOUNDEX_NYSIIS(c1)
Result type: VARCHAR
Returns the NYSIIS Soundex code from a string. The algorithm, devised in 1970 as part of the New York State Identification and Intelligence System (NYSIIS), is part of the New York State Criminal Justice Services, and is 2.7% more accurate than the traditional Russell Soundex. Although not as accurate as the Daitch-Mokotoff Soundex (see SOUNDEX_DM), it is easier to use the output.
Alias: NYSIIS
SELECT SOUNDEX_NYSIIS('XXXXXXX') returns XXXXXXX
SQUEEZE
SQUEEZE(c1)
Result type: TEXT or VARCHAR
Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks.
This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument.
SELECT SQUEEZE ('  Company 2012  ') returns 'Company 2012'
SUBSTR
SUBSTR(c1, loc [, len])
Result type: VARBYTE, VARCHAR, NVARCHAR, and LONG variants
Returns part of c1 starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand.
If len is specified and is less than 1, SUBSTR returns NULL.
The loc parameter determines the start of the substring to be extracted. If loc is less than 0 the position is counted backwards from the end of c1; if loc is greater than 0 the position is counted from the beginning. If loc is 0 the start position is the first character.
After the start of the substring is determined, len characters are extracted. If len is not supplied, the rest of c1 is implied.
If c1 is VARCHAR, CHAR, C, or TEXT, the result is a VARCHAR. If c1 is NCHAR or NVARCHAR, the result is an NVARCHAR. If c1 is a BYTE or VARBYTE, the result is a VARBYTE. If c1 is passed as a long data type, the result is of the same format.
SELECT SUBSTR('Company 2012',9,2) returns '20'
SELECT SUBSTR('Company 2012',9) returns '2012'
SELECT SUBSTR('Company 2012',-9,4) returns 'pany'
SUBSTRING
SUBSTRING(s {FROM | ,} loc [ {FOR | ,} len])
Result type: VARBYTE, VARCHAR, NVARCHAR, and LONG variants
Returns part of string s starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand. The result format is a varchar or nvarchar the size of s.
SELECT SUBSTRING('Company 2012',9,2) returns '20'
SELECT SUBSTRING('Company 2012',9) returns '2012'
SELECT SUBSTRING('Company 2012',-9,4) returns an empty string.
SUBSTRING(s {LIKE | SIMILAR TO | BEGINNING | ENDING } pattern ESCAPE esc_character [modifier])
Result type: VARCHAR and NVARCHAR
Returns part of string s that matches the segment indicated by the escaped double quote characters in the pattern specified:
LIKE and SIMILAR TO
Returns the substring from s that best matches the pattern.
These use a pattern syntax based on LIKE (see LIKE Predicate) and SIMILAR TO (see SIMILAR TO Predicate) predicates. In addition to the respective pattern syntax, both use a pair of quotation marks (") to divide the pattern into three sub-patterns.
An attempt is made to match string s with the complete pattern and to return the portion of string s that corresponds to the middle sub-pattern. If part of string s can be associated with either the middle or an outer sub-pattern, then the middle wins. If the second quotation mark is missing, it will default to the end of pattern, and thus the third pattern would be empty. If no quotation marks are present NULL is returned regardless of the matching.
If the alternation operator (|) is not used, the arrangement with the longest middle pattern match is returned. If an alternation operator is used, these are sequentially used, left to right so the first alternative that generates a total pattern match is taken, and then longest middle match returned.
BEGINNING
Returns the substring from s that starts with the first occurrence of the pattern up to the end of line.
ENDING
Returns the substring from s that ends with the last occurrence of the pattern. The start of the returned text will be the beginning of s.
ESCAPE esc_character
Specifies an escape character, which suppresses any special meaning for the character following it, allowing the character to be entered literally. For details, see LIKE Predicate.
modifier
Controls the matching using the modifier WITHOUT DIACRITICAL or WITHOUT CASE or their positive equivalents WITH DIACRITICAL and WITH CASE, as described in LIKE Predicate.
Default: If no modifier is specified, WITH DIACRITICAL and WITH CASE are implied.
If no match is found, the NULL value is returned.
The result data type and size will match string s.
SELECT SUBSTRING('Company 2016' LIKE 'Company \"%\"' ESCAPE '\') returns '2016'
The whole pattern must match but think of the patterns as three sub-patterns: a prefix pattern, the contained pattern, and the trailing pattern. The prefix and trailing are matched but the substring result will be the text that corresponds to the pattern between the double quote markers.
SELECT SUBSTRING('Company 2016' SIMILAR TO '% \"[0-9]{4}\"'ESCAPE'\') returns '2016'
The part of the full pattern that is between the double quote markers—that is, ‘[0-9]{4}’—will determine the section of matched text returned.
SELECT SUBSTRING('221b Baker Street, London, SL1 4AA, UK'
                 SIMILAR TO '%\"[A-Z]{1,2}[0-9]{1,2} [0-9][A-Z]{2}\"%' ESCAPE '\') returns 'SL1 4AA'
SELECT SUBSTRING('Company 2016' BEGINNING 'test') returns NULL
SELECT SUBSTRING('Company 2016' BEGINNING 'p' ESCAPE '\') returns ‘pany 2016’
SELECT SUBSTRING('Company 2016' ENDING 'p' ESCAPE '\') returns ‘Comp’
SELECT SUBSTRING('Company 2016' BEGINNING 'pan\|20'ESCAPE '\') returns 'pany 2016'
SELECT SUBSTRING('Company 2016' BEGINNING '20\|pan'ESCAPE '\') returns '2016'
SELECT SUBSTRING('Company 2016' BEGINNING '20\|pan'ESCAPE '\') returns '2016'
The alternation operator rules are applied left to right (the same as is used for LIKE and SIMILAR TO).
The CASE and DIACRITICAL qualifiers can be used as well:
SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' escape '\'
      WITH CASE WITH DIACRITICAL) returns 'E'
SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' escape '\'
      WITH CASE WITHOUT DIACRITICAL) returns 'ÈÊË'
SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' escape '\'
      WITHOUT CASE WITH DIACRITICAL) returns 'eE'
SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' escape '\'
      WITHOUT CASE WITHOUT DIACRITICAL) returns 'êëÈÊËèéêëeEÈ'
TRIM
TRIM(c1)
Result type: TEXT or VARCHAR
Returns c1 without trailing blanks. The result has the same length as c1.
ANSI Compliant Version of TRIM:
TRIM([ [BOTH | LEADING | TRAILING] [c1] FROM] c2 )
Result type: Any character string variable
Returns c2 with all occurrences of c1—which can be only one character—removed from the beginning, end, or both, as specified. BOTH is the default. In the absence of c1, the space is assumed.
SELECT 'INGRES' , '['||TRIM('  Company  ' ) ||']'
returns [  Company]
SELECT 'BOTH' , '['||TRIM(BOTH ' ' FROM '  Company  ' ) ||']'
returns [Company]
SELECT 'LEADING' , '['||TRIM(LEADING ' ' FROM '  Company  ' ) ||']'
returns [Company  ]
SELECT 'TRAILING' , '['||TRIM(TRAILING ' ' FROM '  Company  ' ) ||']'
returns [ Company]
UPPERCASE or UPPER
UPPERCASE(c1)
or
UPPER(c1)
Result type: Any character type
Converts all lower case characters in c1 to upper case.
VALIDATE_DIGIT
VALIDATE_DIGIT('scheme', c1)
Result type: INTEGER4
Validates that the check digit in the string c1 is mathematically correct for the specified scheme. The function returns 1 for valid and 0 for invalid.
Restrictions on scheme names and strings are the same as for GENERATE_DIGIT (see GENERATE_DIGIT).
Note:   
The scheme name is case insensitive (can be in mixed case).
You must not include separators, such as dashes or whitespace, in the string.
The VALIDATE_DIGIT function does not enforce any internal formatting rules of the scheme. For example, an EAN is split into a GS1 Prefix, the Company Number, an Item reference, and the check digit. This function only calculates the check digit.
The scheme for GENERATE_DIGIT and VALIDATE_DIGIT functions can be one of the following:
EAN
European Article Number
Synonyms: EAN_13, GTIN, GTIN_13, JAN
The EAN is a bar-coding standard defined by the standards organization GS1. It is used worldwide for marking retail goods. It was developed as a superset of the original 12-digit Universal Product Code (UPC) system. It is also called a Japanese Article Number (JAN) in Japan. UPC, EAN, and JAN numbers are collectively called Global Trade Item Numbers (GTIN).
An EAN string is a 13-character string composed entirely of digits. The check digit is the rightmost character in the string.
EAN_8
European Article Number
Synonyms: GTIN_8, RCN_8
The EAN_8 is an 8-character number derived from the longer EAN_13 and is intended for use on small packages where the longer number would be awkward.
EAN_8 can also be used internally by companies to identify restricted or "own-brand" products to be sold within their stores only. These are often referred to with the synonym RCN_8.
An EAN_8 string is an 8-character string composed entirely of digits. The check digit is the rightmost character in the string.
ISBN
International Standard Book Number
The standard ISBN is a 10-character alphanumeric identifier for printed material. The check digit is the rightmost character and can be either a digit or the alpha 'X'. The other characters in the string must be digits.
ISBN_13
International Standard Book Number
ISBN-13 is the 13-character version of the ISBN. The string is composed entirely of digits. The check digit is the rightmost character in the string.
ISSN
International Standard Serial Number
The ISSN is an 8-character alphanumeric identifier for electronic or print periodicals. The check digit is the rightmost character and can be either a digit or the alpha 'X'. The other characters in the string must be digits.
LUHN
The LUHN algorithm
Most credit cards and many government identification numbers are based on this simple algorithm created by Hans Peter Luhn. For example, the Canadian Social Security number is a nine digit number based on the Luhn algorithm.
The string can be of any permitted length, but must be composed entirely of digits. The check digit is the rightmost character in the string.
LUHN_A
The LUHN algorithm (alphanumeric)
LUHN_A is an extension of the Luhn algorithm that allows for alphabetic characters by assigning numbers 10 to 'A', 11 to 'B', ...35 to 'Z'. It is case insensitive.
The Committee on Uniform Security Identification Procedures (CUSIP) provides a 9-character alphanumeric string based on this algorithm. Also the International Securities Identification Number (ISIN) is a 12-character version of this algorithm.
The string can be of any permitted length, but must be composed entirely of alphanumeric characters. The check digit is the rightmost character in the string.
UPC
Universal Product Code
Synonyms: UPC_A, EAN_12
Used to mark retail goods as per the EAN (see above).
The UPC is composed of 12 digits with the check digit being the rightmost character.
UPC_E
Universal Product Code
The UPC_E is composed of six digits and is intended for smaller packages where a full size UPC_A would be cumbersome. Unlike the UPC_A, the check digit is not appended to the number, but is used to determine the "odd/even" parity assigned to the numbers for when they are encoded as barcode lines.
VERHOEFF
The Verhoeff algorithm
Developed by Jacobus Verhoeff, this algorithm detects all transposition errors and other types of errors that are not detected by the Luhn algorithm.
Like the Luhn algorithm, it can handle strings of any permitted length. The string must be composed entirely of digits and the check digit will be the rightmost character.
VERHOEFFNR
Verhoeff algorithm as described in Numerical Recipes in C.
A variation on the Verhoeff algorithm published in Numerical Recipes in C by Press, Teukolsky, Vetterling, and Flannery.
SOUNDEX_DM vs. SOUNDEX
The advantages of the Daitch-Mokotoff soundex over the standard (Russell) soundex function are:
The extra length of the codes returned avoids false positives on long names with the same base.
For example:
Name
SOUNDEX Code
SOUNDEX_DM Code
Nichols
N242
658400,648400
Nicholson
N242
658460,648460
In this example the soundex() function fails to differentiate between the names, whereas the SOUNDEX_DM() function returns several codes for each name, none of which match any elements in the other.
The ability to return multiple codes allow for the different sounds certain character combinations make.
For example: Does the "ch" in Cherkassy sound like the "ch" in cheese or in Christmas?
The ability to return multiple codes allow a greater chance to match names that the standard soundex will miss.
For example:
Name
SOUNDEX Code
SOUNDEX_DM Code
Schwartsenegger
S632
479465
Shwarzenegger
S625
479465,474659
Schwarzenegger
S625
479465,474659
In this example, the soundex() function fails to match the misspelling 'Schwartsenegger', whereas the SOUNDEX_DM() function generates a string with a matching element (479465) for the other two cases listed.
The disadvantage of the Daitch-Mokotoff soundex over the standard soundex function is the computational overhead required to process a multi-element string.
String Functions That Do Not Accept Long Data Types
The following string functions do not accept LONG VARCHAR, LONG NVARCHAR, or LONG BYTE columns:
BYTEEXTRACT
CHAREXTRACT
CONCAT
INITCAP
LOCATE
LPAD
NOTRIM
REPEAT
REPLACE
RPAD
SHIFT
SQUEEZE
TRIM
To apply any of these functions to a column with a long data type, first coerce the column to an acceptable data type. For example:
SQUEEZE(VARCHAR(long_varchar_column))
If a coercion function is applied to a LONG VARCHAR, LONG NVARCHAR, or LONG BYTE value that is longer than 32,000 characters or bytes, the result is truncated to 32,000 characters or bytes.
String Functions and the UTF8 Character Set
Note:  For the UTF8 character set, the character data is multi-byte string and the actual number of bytes for the data could be more than the number of characters. If the output buffer for any string operation is not sufficiently large to hold the multi-byte string, the result will be truncated at a character boundary.
Date and Time Functions
Date and time functions operate on a date and time input value and return a string, numeric, or date and time value.
ADD_MONTHS
ADD_MONTHS(datetime, n)
Operand type: datetime can be an ANSIDATE, TIMESTAMP, CHAR, VARCHAR, NCHAR, or NVARCHAR; n is an integer.
Result type: Same as the first parameter
Adds the number, which represents months, to the date. If the number is negative then the date is reduced by the number of months.
Example:
SELECT ADD_MONTHS('2012-11-07',2)\g
2013-01-07
DATE_FORMAT
DATE_FORMAT
DATE_FORMAT(datetime, format)
TIME_FORMAT
TIME_FORMAT(datetime, format)
TIME_FORMAT is an alias for DATE_FORMAT.
Operand type: datetime is a DATE, TIME, or TIMESTAMP; format is a character string
Result type: VARCHAR
Returns datetime formatted according to the format string.
The specifiers in the following table can be used in the format string. The “%” character is required before format specifier characters. If the format specifier is inappropriate to the data type of datetime, then NULL is returned.
Specifier
Description
%a
Abbreviated weekday name (Sun..Sat)
%b
Abbreviated month name (Jan..Dec)
%c
Month, numeric (0..12)
%D
Day of the month with English suffix (1st, 2nd, 3rd, …)
%d
Day of the month, numeric (01‑31)
%e
Day of the month, numeric (1‑31)
%f
Microseconds (000000..999999)
%H
Hour (00..23)
%h
Hour (01..12)
%I
Hour (01..12)
%i
Minutes, numeric (00..59)
%j
Day of year (001..366)
%k
Hour (0..23)
%l
Hour (1..12)
%M
Month name (January..December)
%m
Month, numeric (00..12)
%p
AM or PM
%r
Time, 12-hour (hh:mm:ss followed by AM or PM)
%S
Seconds (00..59)
%s
Seconds (00..59)
%T
Time, 24-hour (hh:mm:ss)
%U
Week (00..53), where Sunday is the first day of the week
%u
Week (00..53), where Monday is the first day of the week
%V
Week (01..53), where Sunday is the first day of the week; used with %X
%v
Week (01..53), where Monday is the first day of the week; used with %x
%W
Weekday name (Sunday..Saturday)
%w
Day of the week (0=Sunday..6=Saturday)
%X
Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x
Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y
Year, numeric (four digits)
%y
Year, numeric (two digits)
%%
A literal “%” character
%x
x, for any "x" not listed above
DATE_FORMAT('2010-10-03 22:23:00', '%W %M %Y')
returns 'Sunday October 2010'
DATE_FORMAT('2007-10-03 22:23:00', '%H:%i:%s')
returns '22:23:00'
DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')
returns '4th 00 Thu 04 10 Oct 277'
DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w')
returns '22 22 10 10:23:00 PM 22:23:00 00 6'
DATE_FORMAT('1999-01-01', '%X %V')
returns '1998 52'
DATE_GMT
DATE_GMT(datetime)
Result type: any character data type
Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.
For example, the query:
SELECT DATE_GMT('1-1-98 10:13 PM PST')
returns:
1998_01_02 06:13:00 GMT
while the query:
SELECT DATE_GMT('1-1-1998')
returns:
1998_01_01 00:00:00 GMT
DATE_PART
DATE_PART(unit,date)
Result type: INTEGER8
Returns an integer containing the specified unit component of the input date.
The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter is case sensitive.
Date Portion
How Unit Parameter Is Specified
Year
YEAR, YEARS, YR, YRS
Quarter
QUARTER, QUARTERS, QTR, QTRS
Month
MONTH, MONTHS, MO, MOS
Week
WEEK, WEEKS, WK, WKS
ISO-Week
ISO-WEEK, ISO-WK
Day of month
DAY, DAYS
Day of week
DAYOFWEEK, DOW
Day of year
DAYOFYEAR, DOY
Hour
HOUR, HOURS, HR, HRS
Minute
MINUTE, MINUTES, MIN, MINS
Second
SECOND, SECONDS, SES, SECS
Milliseconds
MILLISECOND
Microseconds
MICROSECOND
Nanoseconds
NANOSECOND
UNIX timestamp
EPOCH
Many of these units can also be derived using the EXTRACT function or using explicit, individual extract functions such as HOUR() or MILLISECOND().
The DATE_PART function is useful in set functions and in ensuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-2012, then:
DATE_PART('MONTH',DATE(date_field))
returns a value of 10 (representing October), and
DATE_PART('DAY',DATE(date_field))
returns a value of 23.
Months are numbered 1 to 12, starting with January. Hours are returned according to the 24-hour clock. Quarters are numbered 1 through 4.
Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.
Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and DATE_PART returns either 52 or 53.
The following table illustrates the difference between Week and ISO-Week:
Date Column
Day of Week
Week
ISO-Week
02-jan-2009
Fri
0
1
04-jan-2009
Sun
0
1
02-jan-2010
Sat
0
53
04-jan-2010
Mon
1
1
02-jan-2011
Sun
0
52
04-jan-2011
Tue
1
1
02-jan-2012
Tue
1
1
04-jan-2012
Thu
1
1
DATE_TRUNC
DATE_TRUNC(unit,date)
Operand type: date can be absolute INGRESDATE, ANSIDATE, TIME, TIMESTAMP
Result type: Same as operand type
Returns a date value truncated to the specified unit. Valid unit parameters are described under DATE_PART.
Where unit is DAY or greater, the day boundary is taken to be in the user's time zone. For example, the following query against a timestamp with time zone value:
SELECT DATE_TRUNC('DAY',TIMESTAMP '2011-02-03 16:12:13.000000000-08:00')
returns
2011-02-03 00:00:00.000000000-08:00
which retains the original time zone offset.
Use the DATE_TRUNC function to group all the dates within the same month or year, and so forth. For example:
DATE_TRUNC('MONTH',DATE('23-oct-1998 12:33'))
returns 1-oct-1998, and
DATE_TRUNC('YEAR',DATE('23-oct-1998'))
returns 1-jan-1998.
Truncation takes place in terms of calendar years and quarters (1-jan, 1-apr, 1-jun, and 1-oct).
To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):
DATE_TRUNC('YEAR',date+'4 mos') - '4 mos'
Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.
DAY
DAY('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the day portion of a date or timestamp.
DAY(TIMESTAMP '2006-12-15 12:30:55.1234') returns 15
DAYOFMONTH
DAYOFMONTH
DAYOFMONTH(datetime)
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER4
Returns the day of the month from the specified datetime value.
DAYOFMONTH(DATE '2011-02-15') returns 15
DAYOFWEEK
DAYOFWEEK
DAYOFWEEK(datetime [,n])
Operand type: datetime is a DATE, TIME, or TIMESTAMP; n is an integer.
Result type: INTEGER4
Returns the day of the week from the specified datetime value, where 1 = Sunday unless n is used to shift the start of week.
DAYOFWEEK(DATE '2011-02-15',4) returns 6
DAYOFWEEK(DATE '2011-02-15') returns 3
DAYOFYEAR
DAYOFYEAR
DAYOFYEAR(datetime)
Returns the ordinal number of day in the year, in the range 1 to 366, for datetime.
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER4
DAYOFYEAR(DATE '2011-02-04') returns 35
DOY
Same as DAYOFYEAR.
DOW
DOW(datetime)
Operand type: datetime is a DATE or TIMESTAMP
Returns the day of the week as a 3‑character string.
DOW('2011-12-25') returns Sun.
EXTRACT
EXTRACT (part FROM datetime)
Operand type: datetime can be a DATE, TIME, TIMESTAMP, or INTERVAL value expression.
Result type: INTEGER4
Extracts a particular field from a datetime value. Part specifies the field to extract.
A part parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.
Valid values for part are:
YEAR
Year field. Range: 0 - 9999
MONTH
Month field. Range: 1 - 12
DAY
Day field. Range: 1 - 31
HOUR
Hour field. Range: 0 - 23
MINUTE
Minute field. Range: 0 - 59
SECOND
Second field. Range: 0 - 59
MILLISECOND
Fractional seconds as milliseconds. Range: 0 - 999.
MICROSECOND
Fractional seconds as microseconds. Range: 0 - 999.
NANOSECOND
Fractional seconds as nanoseconds. Range: 0 - 999.
TIMEZONE_HOUR
Time zone hour offset. Range: ‑12 ‑ 14.
TIMEZONE_MINUTE
Time zone minute offset. Range: 0 - 59
DAYOFWEEK
Day of week with Sunday=1. Range: 1 - 7.
DOW
Day of week with Sunday=1. Range: 1 - 7.
DAYOFYEAR
Day of year. Range: 1 - 366.
DOY
Day of year. Range: 1 - 366.
WEEK
Week of year. Range: 1 - 53.
ISO_WEEK
Week of year ISO 6801.
QUARTER
Year quarter. Range: 1 - 4.
EPOCH
UNIX timestamp (number of seconds since 1-Jan-1970).
Note:  The datetime value cannot be an interval type on the TIMEZONE_HOUR, TIMEZONE_MINUTE, DAYOFWEEK, DAYOFYEAR, WEEK, ISO_WEEK, QUARTER, and EPOCH functions.
Examples:
SELECT EXTRACT (YEAR FROM datecol) FROM datetable;
SELECT EXTRACT (MONTH FROM CURRENT_DATE);
SELECT EXTRACT (HOUR FROM timecol) FROM datetable;
SELECT * FROM tx WHERE EXTRACT(HOUR FROM datetable) = 17;
FROM_UNIXTIME
Operand types: INTEGER4, character string
Formats UNIX timestamp as a date.
FROM_UNIXTIME
FROM_UNIXTIME(i)
Result type: TIMESTAMP WITHOUT TIME ZONE
Returns TIMESTAMP WITHOUT TIME ZONE created from the specified integer, which must be a UNIX time (number of seconds since 1-Jan-1970).
FROM_UNIXTIME(i, format)
Result type: VARCHAR
Returns i formatted in UNIX time, according to the specified format. For valid formats, see DATE_FORMAT.
FROM_UNIXTIME(1196440219)
returns 2007-11-30 10:30:19.000000
FROM_UNIXTIME(1196440219, '%Y %D %M %h:%i:%s %x')
returns 2007 30th November 10:30:19 2007
Note:  Results from this function are from a GMT offset, which may result in a value that is from '1969-12-31 12:00:00.000000' depending on the actual time zone of the server.
GMT_TIMESTAMP
GMT_TIMESTAMP(s)
Result type: Any character data type
Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is ‘yyyy_mm_dd hh:mm:ss GMT’.
For example, the query:
SELECT (GMT_TIMESTAMP (1234567890))
returns:
2009_02_13 23:31:30 GMT
while the query:
(II_TIMEZONE_NAME = AUSTRALIA-BRISBANE)
SELECT date(GMT_TIMESTAMP (1234567890))
returns:
14-feb-2009 09:31:30
HOUR
HOUR('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the hour portion of a time or timestamp.
HOUR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12
INTERVAL
INTERVAL(unit,date_interval)
Result type: FLOAT
Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the month, quarter, and year specifications.
For example, the query:
SELECT(INTERVAL('days', '5 years'))
returns:
1826.213
INTERVAL_DIFF
INTERVAL_DIFF('datetime1', 'datetime2')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTERVAL YEAR TO MONTH
Returns the difference between datetime1 and datetime2 expressed as an INTERVAL YEAR TO MONTH.
This complements subtraction of TIMESTAMPs that yields an INTERVAL DAY TO SECOND and subtraction of ANSIDATEs that yields an integer.
INTERVAL_DIFF('2010-1-1', '2015-3-1') returns 5-2
ISDST
ISDST(date)
Returns true if date occurs during Daylight Saving Time in its time zone.
LAST_DAY
LAST_DAY(datetime)
Operand type: DATE, TIMESTAMP
Result type: DATE or TIMESTAMP depending on input
Returns the last day of the month in which the specified date or timestamp falls. Returns NULL if the argument is invalid.
LAST_DAY(DATE '2003-02-05')
returns 2003-02-28
LAST_DAY(DATE '2004-02-05')
returns 2004-02-29
LAST_DAY(TIMESTAMP '2004-01-01 01:01:01')
returns 2004-01-31
LAST_DAY(DATE '2003-03-32')
returns NULL
MICROSECOND
MICROSECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as microseconds.
MICROSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400
MILLISECOND
MILLISECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as milliseconds.
MILLISECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123
MINUTE
MINUTE('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the minute portion of a time or timestamp.
MINUTE(TIMESTAMP '2006-12-15 12:30:55.1234') returns 30
MONTH
MONTH('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the month portion of a date or timestamp.
MONTH(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12
MONTHS_BETWEEN
MONTHS_BETWEEN
MONTH_BETWEEN(date1, date2)
Operand type: DATE, TIMESTAMP
Result type: FLOAT8
Returns the number of months between date1 and date2, positive if date2 precedes date1 and negative if date1 precedes date2. If the day of date1 is the same as the day in date2 or both dates are the last days of months, then the result is a whole number of months; otherwise, any time portion is also taken into consideration in the difference, where days are treated as 1/31 of a month.
SELECT MONTHS_BETWEEN(DATE‘2010-06-15’, DATE‘2011-06-15’);
-12.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-05-15’);
1.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-30’, DATE‘2011-05-31’);
1.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-06-01’);
0.452
NANOSECOND
NANOSECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as nanoseconds.
NANOSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400000
QUARTER
QUARTER('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the quarter of the calendar year that corresponds to the date or timestamp. Quarters are numbered 1 through 4.
QUARTER(TIMESTAMP '2006-12-15 12:30:55.1234') returns 4
SECOND
SECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the second portion of a time or timestamp.
SECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 55
TIMESTAMPADD
TIMESTAMPADD(interval, n, datetime)
Operand type: Integer, and DATE, TIME, TIMESTAMP
Result type: DATE, TIME, or TIMESTAMP
Returns the datetime after adding the specified number of intervals where:
interval is a keyword from the list: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.
n is an integer expression. The value can be positive or negative as needed.
datetime is a column or a datetime expression that can be a DATE, TIMESTAMP, or TIME value.
TIMESTAMPADD(YEAR, 5, '2010-10-05') returns 2015-10-5
TIMESTAMPDIFF
TIMESTAMPDIFF(interval, datetime1, datetime2)
Operand type: Integer, and DATE, TIME, TIMESTAMP
Result type: Integer type
Returns the integer number of intervals between the two datetimes where:
interval is one of the following keywords: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.
datetime1, datetime2 are columns or datetime expressions whose difference is to be determined in terms of the specific interval, and where datetime1 is a start time, and datetime2 is an end time.
The result is an integer value which can also be negative if datetime1 chronologically follows datetime2.
TIMESTAMPDIFF(YEAR, '2012-01-01', '2008-01-01') returns ‑4
TRUNC
TRUNC(datetime [,str-interval])
TRUNCATE(datetime [,str-interval])
Operand type: datetime is a DATE, TIME, or TIMESTAMP.
Result type: DATE, TIME, or TIMESTAMP
Returns datetime truncated to the unit specified by str-interval. If str-interval is omitted, then datetime is truncated to the nearest day.
The str-interval can be any of the following:
Formats
Description
CC
CENTURY
CENTURIES
Century
D
DY
DAY
Starting day of week
DD
DDD
DAYS
J
Day
HH
HH12
HR
HRS
HOUR
HOURS
Hour
I
IY
IYY
IYYYY
ISO-YR
ISO-YRS
ISO-YEAR
ISO-YEARS
ISO year boundary
IW
ISO-WK
ISO-WEEK
Same day of week as 1st of ISO year
MI
MIN
MINS
MINUTE
MINUTES
Minute
MM
MO
MON
MONTH
MONTHS
RM
MOS
Month
Q
QTR
QTRS
QUARTER
QUARTERS
Quarter
W
Same day of week as 1st of month
WW
Same day of week as 1st of year
Y
YEAR
YY
YYY
YYYY
Year
The precision of the result is the default precision for the input data type. For example, if TRUNC operates on a TIME column, the precision returned is 0; if TIMESTAMP, then 6.
Examples:
SELECT TRUNC('2013-10-11','CC')\g
2000-01-01
SELECT TRUNC('2013-10-11 23:40:10.123456','DD')\g
2013-10-11 00:00:00.000000
SELECT TRUNC('23:40:10.123456','MIN')\g
23:40:00
SELECT TRUNC('2013-10-11 23:40:10.123456')\g
2013-10-11 00:00:00.000000
UNIX_TIMESTAMP
UNIX_TIMESTAMP()
UNIX_TIMESTAMP([datetime])
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER4
Returns a UNIX timestamp (number of seconds since 1 Jan 1970) for the current time (if no argument is specified) or for the specified date.
UNIX_TIMESTAMP(TIMESTAMP '2007-11-30 10:30:19') returns 1196440219
Note:  This function returns a valid result up to the year 2038 because the result is always an INTEGER4 value in the range 1 though MAX_I4.
WEEK
WEEK('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Returns the number of the week of the year that the date or timestamp refers to. Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. Weeks are numbered 1 to 53.
WEEK('2006-12-15 12:30:55.1234') returns 50
WEEK_ISO
WEEKISO('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER1
Extracts the number of the week of the year that the date or timestamp refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begins on Monday, but the first week is the week that has the first Thursday of the year. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.
WEEK_ISO is equivalent to WEEK() function with mode 3.
WEEK_ISO(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50
YEAR
YEAR('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the year portion of a date or timestamp.
YEAR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 2006
_DATE
_DATE(s)
Result type: Any character data type
Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.
For example, the query:
SELECT _DATE(123456)
returns:
2-jan-70
Note:  This function formats a leading space for day values less than 10.
_DATE4
_DATE4(s)
Result type: Any character data type
Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.
For example, with II_DATE_FORMAT set to US, the query:
SELECT _DATE4(123456)
returns:
02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
SELECT _DATE4(123456)
returns:
02/01/1970
_TIME
_TIME(s)
Result type: Any character data type
Returns a five-character string giving the time s seconds after January 1, 1970 GMT, which is then adjusted for your local time zone. The output format is hh:mm (seconds are truncated).
For example, the query:
SELECT _TIME(123456)
returns the value 02:17 for the America-Los_Angeles time zone.
Bitwise Functions
Bitwise functions operate from right to left, with shorter operands padded with binary zeroes to the left.
The bitwise functions are:
BIT_ADD
BIT_ADD(expr, expr)
Operand type: BYTE
Result type: Same as operands
Returns the logical ADD of two byte operands; any overflow is disregarded.
"Logical add" is adding the binary content of the two operands as if they were unsigned binary integers. If the sum does not fit in the result field, the overflow is disregarded and the rest is retained.
For example: C8 + 5A = 0122.
SELECT HEX(BIT_ADD(BYTE(x'C8'), BYTE(x'5A')))
Result is hex 22 because the 01 is dropped as part of the carry over.
BIT_AND
BIT_AND(expr, expr)
Operand type: BYTE, or IPV4 or IPV6 address
Result type: Same as operands
Returns the logical AND of one or more operands.
If two bits are 1, the answer is 1; otherwise the answer is 0.
SELECT BIT_AND(IPV4('255.255.255.0'),IPV4('172.16.254.1'))
returns (as an IPv4)
172.16.254.0
BIT_NOT
BIT_NOT(expr)
Operand type: Single BYTE, or IPV4 or IPV6 address
Result type: Same as operand
Returns the logical NOT of the operand.
SELECT BIT_NOT(IPV4('172.16.254.1'))
returns (as an IPv4):
83.239.1.254
BIT_OR
BIT_OR(expr, expr)
Operand type: BYTE, or IPV4 or IPV6 address
Result type: Same as operands
Returns the logical OR of one or more operands.
If either or both bits are 1, the answer is 1.
SELECT BIT_OR(IPV4('255.255.255.0'),ipv4('172.16.254.1'))\g 
returns (as an IPv4):
255.255.255.1
BIT_XOR
BIT_XOR(expr, expr)
Operand type: BYTE, or IPV4 or IPV6 address
Result type: Same as operands
Returns the logical XOR of one or more operands..
If either, but not both, bits is 1, the answer is 1; otherwise the answer is 0.
SELECT BIT_XOR(IPV4('255.255.255.0'),IPV4('172.16.254.1'))
returns (as an IPv4)
83.239.1.1
INTEXTRACT
INTEXTRACT(byte, n)
Operand types: BYTE, INTEGER4
Result type: INTEGER4
Returns the nth byte as an integer. n is an integer field. Similar to the CHAREXTRACT string function.
INTEXTRACT(X'0A020C04', 3)
returns 12, which is the integer value for the third byte '0C'.
If n is less than 1 or larger than the number of bytes available, then a 0 is returned.
INTEXTRACT(X'0A020C04', -2)
or
INTEXTRACT(X'0A020C04', 5)
both return 0.
Hashing Functions
Hashing functions generate a fixed length "hash" value for given data. They are useful for summarizing a relatively large data item into a small fixed length representation that usually will be distinct from hashes of dissimilar data. Uniqueness is not guaranteed but the chances of two dissimilar items having the same hash value is very low.
HASH
HASH(expr)
Operand type: Any except long types
Result type: INTEGER4
Generates a four-byte numeric value from expressions of all data types except long types. The implicit size for the expression can affect the result.
SELECT HASH(1), HASH(int1(1)), HASH(int2(1)), HASH(int4(1))\g
returns the following single row:
Col1         Col2         Col3          Col4
--------------------------------------------------
-1489118143  1526341860   ‑1489118143   1711753094
Note:  Because the constant 1 is implicitly a short integer, only the return values for HASH(1) and HASH(int2(1)) match. For the remaining columns, the difference in the number of bytes holding the integer leads to a different hash value. Also, the generated hash value is not guaranteed unique, even if the input values are unique.
SHA1SUM
SHA1SUM(expr)
Operand type: Any
Result type: CHAR(40)
Returns the SHA-1 checksum for the input data.
SHA224SUM
SHA224SUM(expr)
Operand type: Any
Result type: CHAR(56)
Returns the SHA-224 checksum for the input data.
SHA256SUM
SHA256SUM(expr)
Operand type: Any
Result type: CHAR(64)
Returns the SHA-256 checksum for the input data.
SHA384SUM
SHA384SUM(expr)
Operand type: Any
Result type: CHAR(96)
Returns the SHA-384 checksum for the input data.
SHA512SUM
SHA512SUM(expr)
Operand type: Any
Result type: CHAR(128)
Returns the SHA-512 checksum for the input data.
Random Number Functions
The random number function is used to generate random values. Use the following statement to set the beginning value for the random functions:
[EXEC SQL] SET RANDOM_SEED [value]
The seed value can be any integer. There is a global seed value and local seed values. The global value is used until you issue SET RANDOM_SEED, which changes the value of the local seed. Once changed, the local seed is used for the whole session. If you are using the global seed value, the seed is changed whenever a random function executes. This means that other users issuing random calls enhance the “randomness” of the returned value.
If you omit the value, Ingres multiplies the process ID by the number of seconds past 1/1/2016 until now. This value generates a random starting point. You can use value to run a regression test from a static start and get identical results.
The random number functions are:
RAND() or RAND(integer)
Result type: float4
Returns a random floating point value between 0.0 and 1.0. If the optional integer parameter is specified, the value is used as a random seed. This function is the same as the RANDOM() function.
RANDOM()
Returns a random integer based on a seed value.
RANDOMF()
Returns a random float based on a seed value between 0 and 1. This is slower than RANDOM, but produces a more random number.
RANDOM(l,h)
Returns a random integer within the specified range (that is, l >= x <= h).
RANDOMF(l,h)
Passing two integer values generates an integer result within the specified range; passing two floats generates a float within the specified range; passing an int and a float causes them to be coerced to an int and generates an integer result within the specified range (that is, l >= x <= h).
SRAND(integer)
Sets the random seed using the specified integer value. Always returns 0. It performs the same function as the SET RANDOM_SEED statement.
IP Network Address Functions
IP network address functions operate on an IPV4 or IPV6 data type or string representing an IPv4 or IPv6 address and return a string in dotted quad or colon-hex format or an IPv4 or IPv6 binary.
INET_NTOP
INET_NTOP(expr)
Operand type: IPV4 or IPV6
Result type: Dotted quad string or colon-hex string, depending on input type
Returns a dotted quad string if the input type is IPV4 or is IPV6 and an IPV4-mapped address.
Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase) if the input type is IPV6 and is not an IPv4-mapped address.
SELECT INET_NTOP(INET_PTON('172.16.254.1'))
returns
'172.16.254.1'
INET_PTON
INET_PTON(expr)
or
IPV4(expr)
Operand type: A string representing an IPv4 network address or an IPv4-mapped IPv6 address
Result type: IPv4 binary
Converts a string in standard IPv4 dotted-quad notation to its IPv4 binary equivalent.
The expression can also be an IPv4-mapped IPv6 address in the form:
"::ffff:xxx:xxx" or "::ffff:n.n.n.n"
and zero-expanded or uppercased variants of the same. An error occurs if the input is an IPv6 style string that does not represent an IPv4-mapped address.
SELECT INET_PTON('172.16.254.1');
returns the following (using hexadeximal representation):
AC10FE01
INET6_NTOP
INET6_NTOP(expr)
Operand type: IPV4 or IPV6
Result type: Colon-hex string
Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase)
SELECT INET6_NTOP(INET6_PTON('172.16.254.1'))
returns
'::ffff:ac10:fe01'
INET6_PTON
INET6_PTON(expr)
or
IPV6(expr)
Operand type: A string representing an IPv4 or IPv6 network address
Result type: IPv6 binary
Converts a string in standard hex-colon notation to an IPv6 binary equivalent. Also converts an IPv4 address in either ordinary dotted-quad or "::ffff:n.n.n.n" mixed quad notation to an IPv4-mapped address ::ffff:n.n.n.n.
The input can be leading-zero-suppressed. Use one "::" to replace the longest sequence of all-zero fields and use lowercase letters. The function also accepts uppercase hexits as input.
SELECT INET6_PTON('2001:0DB8:AC10:FE01:0000:0000:0000:0000')
returns the following (using hexadecimal representation):
20010DB8AC10FE010000000000000000
Last modified date: 08/29/2024