Language Reference Guide : Language Elements : System-defined Functions : SQL Functions
 
Share this page          
SQL Functions
Functions can be used in the following SQL statements:
SELECT
INSERT
UPDATE
DELETE
WHILE
IF
Scalar functions take single-valued expressions as their argument. Aggregate functions take a set of values (for example, the contents of a column in a table) as their argument. Aggregate functions cannot be used in IF or WHILE statements.
Scalar Functions
The scalar functions require either one or two single-value arguments. Scalar functions can be nested to any level.
The types of scalar functions are as follows:
Data type conversion
Numeric
String
Date
Hash
Random number
Note:  If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM t1:
Data Type Conversion Functions
OpenROAD provides the following data type conversion functions:
Name
Operand
Type
Result Type
Description
c(expr)
any
c
Converts any value to a c string
char(expr)
any
char
Converts any value to a char string
date(expr)
c, char,
text, varchar
date
Converts a c, char, varchar, or text string to internal date representation
decimal(expr,
1 < p <31
0 < s <p)
c, char, varchar, text, float, money, integer(1), smallint, integer
decimal
Returns the decimal representation of the argument string
dow(expr)
date
c
Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'); the result length is 3
float4(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
float4
Converts the specified expression to float4
float8(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
float
Converts the specified expression to float
hex(expr)
varchar,
c, char,
text
varchar
Returns the hexadecimal representation of the argument string; the length of the result is twice the length of the argument, because the hexadecimal equivalent of each character requires two bytes
For example, hex('A') returns '61' (ASCII) or 'C1' (EBCDIC).
int1(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
integer1
Converts the specified expression to integer1; floating point values are truncated
int2(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
smallint
Converts the specified expression to smallint; floating point values are truncated
int4(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
integer
Converts the specified expression to integer; floating point values are truncated
int8(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer, boolean
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.
money(expr)
c, char, varchar, text, float, integer(1),
smallint,
integer,
money
Converts the specified expression to internal money representation; rounds floating point values, if necessary
nvarchar(expr [, len])
any
nvarchar
Converts argument to nvarchar Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr.
text(expr)
any
text
Converts any value to a text string; this function removes any trailing blanks from c or char string expressions
to_char(datetime [,format])
ansidate, timestamp, timstamp with time zone, or timstamp with local time zone
varchar
Converts a datetime or interval value to a value of varchar 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.
 
 
 
Format can be:
punctuation "text"--Preserves literal punctuation and quoted text
AD, A.D--AD indicator
AM, A.M., PM, P.M.--AM/PM indicator
CC--Century
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
E, EE--Era name
FF [1...9]--Fractional seconds
FM--Toggle whether formats have leading or trailing blanks
FX--Toggle whether exact matching is required
HH--Hour od day 1 - 12
HH12--Hour of day 1 - 12
HH24--Hour of day 0 - 23
IW--ISO week of year 1 - 53
IYYY, IYY, IY, I--Last 4, 3, 2, 1 digits of ISO year
 
 
 
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
Q--Quarter 1 - 4
RM--Month in Roman numerals
RR--Round year in 2 digits
RRRR--Round year in 2 or 4 digits
SS--Seconds since midnight 1 - 86399
TZH--Time zone hour
WW--Week of year 1 - 53
W--Week of month 1 - 5
X--Decimal
Y,YYY--Year with comma in this position
YEAR--Year spelled out
YYYY, YYY, YY, Y--Last 4, 3, 2, or 1 digits of year
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
unhex(expr)
any
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.
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.
varchar(expr)
any
varchar
Converts any value to a varchar string; this function removes any trailing blanks from c or char string expressions
Numeric Functions
SQL supports the numeric functions listed in the following table:
Numeric Function Name
Operand Type
Result Type
Description
ABS(n)
all numeric types and money
same as n
Absolute value of n.
ACOS(n)
all numeric types
float
Arccosine of cosine value n
ASIN(n)
all numeric types
float
Arcsine value of sine value n
ATAN(n)
all numeric types
float
Arctangent of n; returns a value from (-pi/2) to pi/2.
ATAN2 (x, y)
all numeric types
float
Arctangent of angle defined by coordinate pair (x, y)
CEIL(n)
CEILING(n)
all numeric types
decimal
Returns the smallest integer greater than or equal to the specified numeric expression.
COS(n)
all numeric types
float
Cosine of n; returns a value from -1 to 1.
EXP(n)
all numeric types and money
float
Exponential of n.
FLOOR(n)
all numeric types
decimal
Returns the largest integer less than, or equal to, the specified numeric expression.
LOG(n)
LN(n)
all numeric types and money
float
Natural logarithm of n.
MOD(n,b)
integer, smallint, integer1, decimal
same as b
n modulo b. The result is the same data type as b.
Decimal values are truncated.
PI()
None
float
Value of pi (ratio of the circumference of a circle to its diameter)
ROUND(n,i)
all numeric types
decimal
Rounds value at the i'th place right or left of the decimal, depending on whether i is greater or less than 0.
SIGN(n)
all numeric types
integer
-1 if n < 0, 0 if n = 0, +1 if n > 0
SIN(n)
all numeric types
float
Sine of n; returns a value from -1 to 1.
SQRT(n)
all numeric types and money
float
Square root of n.
TAN(n)
all numeric types
float
Tangent value of angle n
TRUNC(x ,y)
all numeric types
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.
For trigonometric functions (COS, SIN, TAN), specify argument in radians. To convert degrees to radians, use the following formula:
radians = degrees / 360 * 2 * pi()
The functions ACOS, ASIN, ATAN, AND ATAN2 return a value in radians.
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, and
CONCAT(CONCAT(x.lastname, ', '), x.firstname)
concatenates x.lastname with a comma and concatenates x.firstname with the first concatenation result.
The + operator can also be used to concatenate strings:
x.lastname + ', ' + x.firstname
String Functions and the UTF-8 Character Set
Note:  For the UTF-8 character set, the character data is multibyte 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 multibyte string, the result will be truncated at a character boundary.
String Functions Supported
The following table lists the string functions supported in SQL.
The expressions c1 and c2, representing function arguments, can be any of the string types (char, varchar, long varchar, c, text, byte, varbyte, long varbyte) or any of the Unicode types (nchar, nvarchar, long nvarchar), except where noted. The expressions len, n, n1, n2 or nshift, representing function arguments, are the integer type. For string functions operating on one of the string types, the integer arguments represent character (or 8-bit octet) counts or offsets. For string functions operating on one of the Unicode types, the integer arguments represent “code point” (or 16-bit Unicode characters) counts or offsets.
String Function Name
Result Type
Description
ASCII(v1)
any character type
Returns the character equivalent of the value v1, which is an expression of either character or numeric type.
CHAREXTRACT(c1,n)
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. It does not support long varchar or long nvarchar arguments.
CHARACTER_LENGTH(c1)
integer
Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function.
This function does not support nchar and nvarchar arguments.
CHR(n)
character
Converts integer into corresponding ASCII code. If n is greater than 255, the conversion is performed on n mod 256.
CONCAT(c1,c2)
any character or Unicode data type, byte
Concatenates one string to another. The result size is the sum of the sizes of the two 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.
This function does not support long nvarchar arguments.
GENERATE_DIGIT('scheme', 'c1')
varchar(2)
Generates the check digit for the string c1 using the specified encoding scheme.
Supported schemes are described under 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.
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.
This function does not support nchar and nvarchar arguments.
INITCAP(c1)
any character or Unicode data type
Converts all initial characters in c1 to upper case.
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'
LEFT(c1,len)
any character or Unicode data 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.
This function does not support long nvarchar arguments.
LENGTH(c1)
smallint
(for long varchar, returns 4-byte integer)
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.
LOCATE(c1,c2)
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.
This function does not support long varchar or long nvarchar arguments.
LOWERCASE(c1) or LOWER(c1)
any character or Unicode data type
Converts all upper case characters in c1 to lower case.
LPAD(expr1, n [, expr2])
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(expr)
any character data type
Returns character expression with leading blanks removed.
OCTET_LENGTH(c1)
integer
Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function.
PAD(c1)
text, varchar, or nvarchar
Returns c1 with trailing blanks appended to c1. For example, if c1 is a varchar string that could hold 50 characters but only has two characters, then PAD(c1) appends 48 trailing blanks to c1 to form the result.
RIGHT(c1,len)
any character or Unicode data 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.
This function does not support long nvarchar arguments.
RPAD(expr1, n [, expr2])
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(expr)
any character data type
Returns character expression with trailing blanks removed.
SHIFT(c1,nshift)
any character or Unicode data 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.
This function does not support long varchar or long nvarchar arguments.
SIZE(c1)
smallint
Returns the declared size of c1 without removal of trailing blanks.
SOUNDEX(c1)
any character data 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 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.
This function does not support long varchar or Unicode arguments.
SOUNDEX_DM(c1)
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.
This function does not support long varchar or Unicode arguments.
For more information, see SOUNDEX_DM vs. SOUNDEX).
SQUEEZE(c1)
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.
This function does not support long varchar or long nvarchar arguments.
SUBSTR(c1, loc [, len])
varbyte, varchar, nvarchar, 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 omitted, 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'
TRIM(c1)
text or varchar
Returns c1 without trailing blanks. The result has the same length as c1.
This function does not support long varchar or long nvarchar arguments.
UPPERCASE(c1) or UPPER(c1)
any character data type
Converts all lower case characters in c1 to upper case.
VALIDATE_DIGIT('scheme', c1)
integer
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.
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 is case insensitive and 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. 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 Concatenation Results
The following table shows the results of concatenating expressions of various character data types:
1st String
2nd String
Trim Blanks
Result Type
from 1st?
from 2nd?
c
c
Yes
--
C
c
text
Yes
--
C
c
char
Yes
--
C
c
varchar
Yes
--
C
text
c
No
--
C
char
c
Yes
--
C
varchar
c
No
--
C
text
text
No
No
text
text
char
No
Yes
text
text
varchar
No
No
text
char
text
Yes
No
text
varchar
text
No
No
text
char
char
No
--
char
char
varchar
No
--
char
varchar
char
No
--
char
varchar
varchar
No
No
varchar
nchar
nchar
No
No
nchar
nchar
nvarchar
No
No
nchar
nvarchar
nchar
No
No
nchar
nvarchar
nvarchar
No
No
nvarchar
When concatenating more than two operands, expressions are evaluated from left to right. For example:
varchar + char + varchar
is evaluated as:
(varchar+char)+varchar
To control concatenation results for strings with trailing blanks, use the TRIM and PAD functions.
Date Functions
OpenROAD supports functions that derive values from absolute dates and from interval dates. These functions operate on rows or variables that contain date values. An additional function, dow(), returns the day of the week (for example, mon, tue) for a specified date. The dow() function is described in Data Type Conversion Functions.
The following date functions extract the specified portion of a date, time, or timestamp. They can be used with the ingresdate data types.
YEAR()
Extracts the year portion of a date.
QUARTER()
Extracts the quarter corresponding to the date. Quarters are numbered 1 through 4.
MONTH()
Extracts the month portion of a date.
WEEK()
Extracts the number of the week of the year that the date 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_ISO()
Extracts the number of the week of the year that the date refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begin 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.
DAY()
Extracts the day portion of a date.
HOUR()
Extracts the hour portion of a time.
MINUTE()
Extracts the minute portion of a time.
SECOND()
Extracts the second portion of a time.
MICROSECOND()
Extracts the fractions of seconds portion of a time as microseconds.
NANOSECOND()
Extracts the fractions of seconds portion of a time as nanoseconds.
Examples:
DAY('2006-12-15 12:30:55.1234') returns 15
SECOND('2006-12-15 12:30:55.1234') returns 55.1234
Date Functions for Date(ingresdate) Data Type
Note:  The functions described in this section apply only to the date(ingresdate) data type.
SQL supports functions that derive values from absolute dates and from interval dates. These functions operate on columns that contain date values. An additional function, DOW(), returns the day of the week (mon, tue, and so on) for a specified date. For a description of the DOW() function, see Data Type Conversion Functions.
Some date functions require specifying of a unit parameter; unit parameters must be specified using a quoted string. The following table lists valid unit parameters:
Date Portion
How Specified
Second
SECOND, SECONDS, SES, SECS
Minute
MINUTE, MINUTES, MIN, MINS
Hour
HOUR, HOURS, HR, HRS
Day
DAY, DAYS
Week
WEEK, WEEKS, WK, WKS
ISO-Week
ISO-WEEK, ISO-WK
Month
MONTH, MONTHS, MO, MOS
Quarter
QUARTER, QUARTERS, QTR, QTRS
Year
YEAR, YEARS, YR, YRS
The following table lists the date functions:
Date Function Name
Format (Result)
Description
DATE_TRUNC(unit,date)
date
Returns a date value truncated to the specified unit.
DATE_PART(unit,date)
integer
Returns an integer containing the specified (unit) component of the input date.
DATE_GMT(date)
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 the following value:
1998_01_01 06:13:00 GMT
while the query:
SELECT DATE_GMT(‘1-1-1998’)
returns:
1998_01_01 00:00:00 GMT
GMT_TIMESTAMP(s)
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 the following value:
2009_02_13 23:31:30 GMT
while the query:
(II_TIMEZONE_NAME = AUSTRALIA-QUEENSLAND)
SELECT date(GMT_TIMESTAMP (1234567890))
returns:
14-feb-2009 09:31:30
INTERVAL (unit,date_interval)
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 mos, qtrs, and yrs specifications.
For example, the query:
SELECT(INTERVAL(‘days’, ‘5 years’))
returns the following value:
1826.213
ISDST(date)
integer
Returns 1 if date falls within Daylight Saving Time for the session timezone, else 0.
_DATE(s)
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 the following value:
2-jan-70
Note that this function formats a leading space for day values less than 10.
_DATE4(s)
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 the following value:
02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
SELECT _DATE4(123456)
returns the following value:
02/01/1970
_TIME(s)
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 NA-PACIFIC time zone.
Truncate Dates using DATE_TRUNC Function
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.
Using DATE_PART
The DATE_PART function is useful in set functions and in assuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-1998, 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-1998
Fri
0
1
04-jan-1998
Sun
0
1
02-jan-1999
Sat
0
53
04-jan-1999
Mon
1
1
02-jan-2000
Sun
0
52
04-jan-2000
Tue
1
1
02-jan-2001
Tue
1
1
04-jan-2001
Thu
1
1
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'
HASH Functions
The HASH function is used to generate a four-byte numeric value from expressions of all data types except long data types. Note that the implicit size for the expression can affect the result. For example:
SELECT HASH(int1(1)), HASH(int2(1)), HASH(int4(1))\g
returns the following single row:
Col2
Col3
Col4
1526341860
-920527466
-1447292811
Note:  Constant values such as HASH(1) will return a value based on the implicit datatype of the constant. For Ingres this is equivalent to HASH(int2(1)). For OpenROAD this is equivalent to HASH(int4(1)).
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/1970 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.
Aggregate Functions
Aggregate functions take a set of values as their argument.
Aggregate functions include the following:
Unary
Binary
Count
Unary Aggregate Functions
A unary aggregate function returns a single value based on the contents of a column. Aggregate functions are also called set functions.
Note:  Aggregate functions used in OpenROAD can only be coded inside SQL statements.
The following example uses the sum aggregate function to calculate the total of salaries for employees in department 23:
SELECT SUM (employee.salary)
       FROM employee
       WHERE employee.dept = 23;
SQL Aggregate Functions
The following table lists SQL aggregate functions:
Aggregate Function Name
Result Data Type
Description
ANY
integer
Returns 1 if any row in the table fulfills the where clause, or 0 if no rows fulfill the where clause.
AVG
float, money, date (interval only)
Average (sum/count)
The sum of the values must be within the range of the result data type.
COUNT
integer
Count of non-null occurrences
MAX
same as argument
Maximum value
MIN
same as argument
Minimum value
SUM
integer, float, money, date (interval only)
Column total
STDDEV_POP
float
Compute the population form of the standard deviation (square root of the population variance of the group).
STDDEV_SAMP
float
Computes the sample form of the standard deviation (square root of the sample variance of the group).
VAR_POP
float
Computes the population form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values).
VAR_SAMP
float
Computes the sample form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values minus 1).
The general syntax of an aggregate function is as follows:
function_name ([DISTINCT | ALL] expr)
where function_name denotes an aggregate function and expr denotes any expression that does not include an aggregate function reference (at any level of nesting).
To eliminate duplicate values, specify DISTINCT. To retain duplicate values, specify ALL, which is the default. DISTINCT is not meaningful with the functions MIN and MAX because these functions return single values (and not a set of values).
Nulls are ignored by the aggregate functions, with the exception of COUNT, as described in COUNT(*) Function.
Binary Aggregate Functions
Ingres supports a variety of binary aggregate functions that perform a variety of regression and correlation analysis. For all of the binary aggregate functions, the first argument is the independent variable and the second argument is the dependent variable.
The following table lists binary aggregate functions:
Binary Aggregate Function Name
Result Data Type
Description
REGR_COUNT
(indep_parm, dep_parm)
integer
Count of rows with non-null values for both dependent and independent variables.
COVAR_POP
(indep_parm, dep_parm)
float
Population covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows).
COVAR_SAMP
(indep_parm, dep_parm)
float
Sample covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows minus 1).
CORR
(indep_parm, dep_parm)
float
Correlation coefficient (ratio of the population covariance divided by the product of the population standard deviation of the independent variable and the population standard deviation of the dependent variable).
REGR_R2
(indep_parm, dep_parm)
float
Square of the correlation coefficient.
REGR_SLOPE
(indep_parm, dep_parm)
float
Slope of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.
REGR_INTERCEPT
(indep_parm, dep_parm)
float
Y-intercept of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.
REGR_SXX
(indep_parm, dep_parm)
float
Sum of the squares of the independent variable.
REGR_SYY
(indep_parm, dep_parm)
float
Sum of the squares of the dependent variable.
REGR_SXY
(indep_parm, dep_parm)
float
Sum of the product of the independent variable and the dependent variable.
REGR_AVGX
(indep_parm, dep_parm)
float
Average of the independent variables.
REGR_AVGY
(indep_parm, dep_parm)
float
Average of the dependent variables.
COUNT(*) Function
The COUNT function can take the wildcard character (*) as an argument. This character is used to count the number of rows in a result table, including rows that contain nulls.
For example, the following statement counts the number of employees in department 23:
SELECT COUNT(*)
       FROM employee
       WHERE dept = 23;
The asterisk (*) argument cannot be qualified with ALL or DISTINCT.
Because COUNT(*) counts rows rather than columns, it does not ignore nulls. Consider the following table:
Name
Exemptions
Smith
0
Jones
2
Tanghetti
4
Fong
Null
Stevens
Null
Running:
COUNT(exemptions)
returns the value of 3, whereas:
COUNT(*)
returns 5.
Except COUNT, if the argument to an aggregate function evaluates to an empty set, the function returns a null. The COUNT function returns a zero.
Aggregate Functions and Decimal Data
Given decimal arguments, aggregate functions (with the exception of COUNT) return decimal results.
The following table explains how to determine the scale and precision of results returned for aggregates with decimal arguments:
Function Name
Precision of Result
Scale of Result
COUNT
Not applicable
Not applicable
SUM
39
Same as argument
AVG
39
Scale of argument + 1 (to a maximum of 39)
MAX
Same as argument
Same as argument
MIN
Same as argument
Same as argument
GROUP BY Clause with Aggregate Functions
The GROUP BY clause allows aggregate functions to be performed on subsets of the rows in the table. The subsets are defined by the GROUP BY clause.
For example, the following statement selects rows from a table of political candidates, groups the rows by party, and returns the name of each party and the average funding for the candidates in that party.
SELECT party, AVG(funding)
       FROM candidates
       GROUP BY party;
Restrictions on the Use of Aggregate Functions
The following restrictions apply to the use of aggregate functions:
Aggregate functions cannot be nested.
Aggregate functions can be used only in SELECT or HAVING clauses.
If a SELECT or HAVING clause contains an aggregate function, columns not specified in the aggregate must be specified in the GROUP BY clause. For example:
SELECT dept, AVG(emp_age)
FROM employee
GROUP BY dept;
The above SELECT statement specifies two columns, dept and emp_age, but only emp_age is referenced by the aggregate function, AVG. The dept column is specified in the GROUP BY clause.
IFNULL Function
The IFNULL function specifies a value other than a null that is returned to your application when a null is encountered. The ifnull function is specified as follows:
IFNULL(v1,v2)
If the value of the first argument is not null, IFNULL returns the value of the first argument. If the first argument evaluates to a null, IFNULL returns the second argument.
For example, the SUM, AVG, MAX, and MIN aggregate functions return a null if the argument to the function evaluates to an empty set. To receive a value instead of a null when the function evaluates to an empty set, use the IFNULL function, as in this example:
IFNULL(SUM(employee.salary)/25, -1)
IFNULL returns the value of the expression sum(employee.salary)/25 unless that expression is null. If the expression is null, the IFNULL function returns -1.
Note:  If an attempt is made to use the IFNULL function with data types that are not nullable, such as system_maintained logical keys, a runtime error is returned.
Note:  If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM t1:
IFNULL Result Data Types
If the arguments are of the same data type, the result is of that data type. If the two arguments are of different data types, they must be of comparable data types. For a description of comparable data types, see Assignment Operations.
When the arguments are of different but comparable data types, the DBMS Server uses the following rules to determine the data type of the result:
The result type is always the higher of the two data types; the order of precedence of the data types is as follows:
date > money > float4 > float > decimal > integer > smallint > integer1
and
c > text > char > varchar > byte > byte varying
The result length is taken from the longest value. For example:
IFNULL (VARCHAR (5), c10)
results in c10.
The result is nullable if either argument is nullable. The first argument is not required to be nullable, though in most applications it is nullable.
IFNULL and Decimal Data
If both arguments to an IFNULL function are decimal, the data type of the result returned is decimal, and the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the result is determined as follows:
Precision—The largest number of digits to the left of the decimal point (precision - scale) plus largest scale (to a maximum of 39)
Scale—The largest scale