Result type: INTEGER

Returns 1 if any row in the table fulfills the where clause, or 0 if no rows fulfill the WHERE clause.

Result type: FLOAT, MONEY, DATE (INTERVAL only)

Returns the average (sum / count). The sum of the values must be within the range of the result data type.

Result type: INTEGER8

Returns count of non-null occurrences.

For example, count the number of employees allocated to a department:

COUNT_BIG is a synonym for COUNT.

Result type: INTEGER8

Returns count of 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 'PERS':

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:

The expression:

returns the value of 3, whereas:

returns 5.

If the argument to the COUNT function evaluates to an empty set, COUNT returns zero, which is in contrast to other functions, which return a null.

Result type: INTEGER

Returns an integer value based on its parameters.

It can be used to simplify a query that needs many GROUP BY levels by letting you more easily express row filtering conditions.

The GROUPING function can have multiple parameters, each of which must be a column or expression in the GROUP BY clause of an aggregate query. The parameters from right to left are represented in the function result by bits from right to left.

When CUBE, ROLLUP, or GROUPING SETS syntax is used, result rows from the query can be formed based on the grouping of rows according to different combinations of the grouping columns or expressions. If a result row is based on a grouping that includes a parameter of the grouping function, its bit position in the GROUPING() function result is 0, otherwise 1. This helps you determine which groupings each of the result rows comes from.

For example:

produces results like:

The numbers 0, 1, 2, and 3 are the results of the GROUPING() function. Rows with both sno and pno have a GROUPING() value of 0 because they result from the grouping of both sno and pno. Rows with only sno (superaggregate rows grouped only on sno) have a value of 1 because the rightmost parameter (pno) does not contribute to the grouping. Rows with only pno (superaggregates grouped only on pno) have a value of 2 because the next parameter (sno—in bit position 2) does not contribute. And the last row has a value of 3 because neither sno nor pno are involved in the grouping.

Result type: Same as argument

Returns maximum value.

Operand type: All numeric types and ANSIDATE

Result type: Same as argument

Returns an expression that is the middle of a sorted list of the input expressions. Nulls are ignored. If the number of input expressions is odd, the result is from the expression list. If it is even, the result is the interpolation between the two middle expressions, rounding up for integer expressions.

For example:

Result type: Same as argument

Returns minimum value.

Operand type: All numeric types and ANSIDATE

Result type: Same as argument

Returns a value that corresponds to the given fraction (n) in the sort order, where 0 <= n <= 1. Sorts the input expressions and returns a single expression that is greater than n of the input expressions. Nulls are ignored. For example, if n is .8, the aggregate result will be >= 80% of the input values. The result may be an interpolated value if no input exactly delineates the percentile, rounding up for integer expressions.

PERCENTILE_CONT (.5)... is the same as MEDIAN.

For example:

Result type: FLOAT

Computes the population form of the standard deviation (square root of the population variance of the group).

Result type: FLOAT

Computes the sample form of the standard deviation (square root of the sample variance of the group).

Result type: INTEGER, FLOAT, MONEY, DATE (INTERVAL only)

Returns column total.

Result type: 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).

Result type: 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).

Result type: FLOAT

Returns 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).

Result type: FLOAT

Returns 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).

Result type: FLOAT

Returns 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).

Result type: FLOAT

Returns average of the independent variables.

Result type: FLOAT

Returns average of the dependent variables.

Result type: INTEGER

Returns count of rows with non-null values for both dependent and independent variables.

Result type: FLOAT

Returns Y-intercept of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.

Result type: FLOAT

Returns square of the correlation coefficient.

Result type: FLOAT

Returns slope of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.

Result type: FLOAT

Returns the sample corrected sum of the squares of the independent variable.

Result type: FLOAT

Returns sum of the product of the independent variable and the dependent variable.

Result type: FLOAT

Returns the sample corrected sum of the squares of the dependent variable.

Concatenates expressions within a group.

Result type: VARCHAR(4000). Longer strings are truncated. The length of the result can be configured using the [engine] listagg_group_maxlen parameter.

where:

The following LISTAGG examples are based on this table:

Concatenate the names of all employees, ordered by last name (LISTAGG used as a simple aggregate):

Same as previous query, but use a delimiter to separate names with a comma:

Concatenate employees in each department, ordered by last name (LISTAGG used as a regular aggregate with GROUP BY):

Concatenate employee names in the same department, ordered by last name, partitioned by department (LISTAGG used as a windowing aggregate):

Produces one row per group, or if there is no group_by_clause, a single row

where:

The following query shows the employee number (empno) of the highest paid employee in each department:

Specifies an analytical function or an aggregate function.

A select list can have more than one function, each with a different OVER clause.

Specifies a named window specification defined elsewhere in the query or a parenthesized optional partitioning definition, ordering definition, and/or frame definition:

An empty window specification, that is, OVER(), causes the function results to be computed across all rows in the table.

Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values of their sort specification have the same RANK() value and result in no gaps in the list of ranks. For example: 1, 2, 2, 3, 4, 4, 4, 5.

The following query ranks each employee in a department based on his salary. When two employees have the same salary they are assigned the same rank. When multiple rows have the same rank, the next rank in the sequence is consecutive.

The following query uses the DENSE_RANK function and then gets only the rows ranked 1 from each group. You must provide a correlation name (in this case, x) for the subquery:

Produces one row per input row. The same value is returned for every row in the partition, or if there is no partition_by_clause, every row in the result.

where:

The following query ranks each employee in a department based on his salary:

Returns a scalar column value from a row before (LAG) or after (LEAD) the current row. Use LAG to compare values in the current row with values in a previous row. Use LEAD to compare values in the current row with values in a following row.

LAG and LEAD are useful for comparing values across multiple rows—for example, across time periods.

where:

The following query shows the date, the weather, and the previous day's weather:

The following query finds the salary of each employee in department 300 and lists the next higher salary:

The following example demonstrates the use of IGNORE NULLS:

Note the difference in results between the lag2 and lag2ign columns.

For the fourth row, for RESPECT NULLS the value two rows above is 2.

However, for IGNORE NULLS the second non-null value above is 1.

Divides the rows in an ordered partition into n groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

NTILE can be used, for example, to see what quartile, decile, or percentile a row is in.

The following query divides the employees in Department 400 into 4 groups by salary:

When the number of rows is not divisible by n, the later rows will have the smaller number of rows, so the first and second quartiles have 4 rows whereas the third and fourth have only 3.

Calculates the relative rank of a row within a group of rows. PERCENT_RANK returns a number between 0 and 1, which represents the percentage of rows in the group that are less than the current row. If a partition has exactly one row, its percent_rank() is 0. Percent_rank() for the highest value in the group will always be 1.

Use this function to determine the relative standing of a value within a result set.

The following query calculates, for each employee, the percent rank of the employee's salary in the department:

Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values of their sort specification have the same RANK() value and result in gaps in the list of ranks. For example: 1, 2, 2, 4, 5, 5, 5, 8.

The following query ranks each employee in a department based on his salary. When two employees have the same salary they are assigned the same rank. When multiple rows have the same rank, the next rank in the sequence is not consecutive.

Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values in their sort specification are ordered arbitrarily. For example: 1, 2, 3, 4, 5.

The following query assigns a consecutive number to each row. Rows with matching numbers are ordered arbitrarily.

Operand type: All numeric types and MONEY

Result type: Same as n

Absolute value of n

Operand type: All numeric types

Result type: FLOAT

Arccosine of cosine value n

Operand type: All numeric types

Result type: FLOAT

Arcsine value of sine value n

Operand type: All numeric types

Result type: FLOAT

Arctangent of n; returns a value from (-pi/2) to pi/2.

Operand type: All numeric types

Result type: FLOAT

Arctangent of angle defined by coordinate pair (x, y)

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.

Operand type: All numeric types

Result type: FLOAT

Cosine of n; returns a value from -1 to 1.

Operand type: All numeric types and MONEY

Result type: FLOAT

Exponential of 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.

Operand type: All numeric types and MONEY

Result type: FLOAT

Natural logarithm of n

Operand type: INTEGER, SMALLINT, INTEGER1, DECIMAL

Result type: Same as b

n modulo b. The result is the same data type as b. Decimal values are truncated.

Operand type: None

Result type: FLOAT

Value of pi (ratio of the circumference of a circle to its diameter)

Operand type: All numeric types

Result type: FLOAT

x to the power of y (identical to x ** y)

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.

Operand type: All numeric types and MONEY

Result type: INTEGER

Returns -1 if n < 0, 0 if n = 0, +1 if n > 0

Operand type: All numeric types

Result type: FLOAT

Sine of n; returns a value from -1 to 1.

Operand type: All numeric types and MONEY

Result type: FLOAT

Square root of n

Operand type: All numeric types

Result type: FLOAT

Tangent value of angle n

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.

Result type: Any character type

Returns the character equivalent of the value v1, which is an expression of any type.

Result type: INTEGER

Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function.

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.

SELECT CHAREXTRACT('company',4) returns 'p'

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'.

Result type: Any character or Unicode type, BYTE

Concatenates two or more 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.

Wrong:

Correct:

Result type: Any character or Unicode type

Converts all initial characters in c1 to upper case.

returns:'This Is The Final Version (Version:5.A;6) Of Leonard's Will'

Result type: FLOAT4

Calculates the Jaro-Winkler similarity between two VARCHAR strings. This is returned as a float value between 0 and 1, where 0.0 means no similarities and 1.0 means the strings are identical.

SELECT JARO_WINKLER('same','same') returns 1.0

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'

Result type: SMALLINT

If c1 is a fixed-length 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.

SELECT LENGTH ('Company') returns 7

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

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

or

Result type: Any character or Unicode type

Converts all upper case characters in c1 to lower case.

SELECT LOWER ('Company') returns 'company'

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'

Result type: Any character type

Returns character expression with leading blanks removed.

SELECT LTRIM ('  Company') returns 'Company'

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.

Result type: INTEGER

Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function.

octet(col1 VARCHAR(10), col2 CHAR(10));

INSERT INTO octet VALUES ('Company', 'Company')

SELECT OCTET_LENGTH (col1) returns 7

SELECT LENGTH (col1) returns 7

SELECT OCTET_LENGTH (col2) returns 10

SELECT LENGTH (col2) returns 7

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’).

Result type: Any character type

Returns c1 (a character string) repeated n times.

SELECT REPEAT ('-',10) returns '----------'

SELECT REPEAT ('str',3) returns 'strstrstr'

Result type: Any 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'

Result type: Any 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'

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-'

Result type: Any character type

Returns character expression with trailing blanks removed.

SELECT LENGTH(RTRIM('Company     ')) returns 7

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    '

Result type: SMALLINT

Returns the declared size of c1 without removal of trailing blanks.

SELECT SIZE('Company') returns 7

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 only the next strings in the index.

SELECT SOUNDEX('Company') returns 'C515'

SELECT SOUNDEX ('Company2012') returns 'C515'

Result type: 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'

Result type: VARCHAR, NVARCHAR

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.

SELECT SUBSTR('Company 2012',9,2) returns '20'

SELECT SUBSTR('Company 2012',9) returns '2012'

SELECT SUBSTR('Company 2012',-9,4) returns 'pany'

Result type: VARCHAR, NVARCHAR

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. The result format is a varchar or nvarchar the size of c1. Unlike SUBSTR, loc and len must be positive values.

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.

Result type: VARCHAR

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. This function performs a case-sensitive match when searching for delim.

Result type: VARCHAR

Returns c1 without trailing blanks. The result has the same length as c1.

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 'DEFAULT' , '['||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]

or

Result type: Any character type

Converts all lower case characters in c1 to upper case.

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:

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.

returns 'Sunday October 2010'

returns '22:23:00'

returns '4th 00 Thu 04 10 Oct 277'

returns '22 22 10 10:23:00 PM 22:23:00 00 6'

returns '1998 52'

Result type: INTEGER

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.

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:

returns a value of 10 (representing October), and

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:

Operand type: date can be absolute ANSIDATE, TIME, TIMESTAMP

Result type: Same as operand type

Returns a date value truncated to the specified unit.

The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.

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:

returns

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:

returns 1998-10-01, and

returns 1998-01-01.

Truncation takes place in terms of calendar years and quarters (January 1, April 1, June 1, October 1).

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):

Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.

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

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER.

Returns the day of the month from the specified datetime value.

DAYOFMONTH(DATE '2011-02-15') returns 15

Operand type: datetime is a DATE, TIME, or TIMESTAMP; n is an integer.

Result type: INTEGER

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

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: INTEGER

DAYOFYEAR(DATE '2011-02-04') returns 35

Same as DAYOFYEAR.

Operand type: datetime can be a DATE, TIME, TIMESTAMP, or INTERVAL value expression.

Result type: INTEGER

Extracts a particular field from a date/time 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:

Examples:

Operand types: INTEGER, character string

Formats UNIX timestamp as a date.

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).

Result type: VARCHAR

Returns i formatted in UNIX time, according to the specified format. For valid formats, see DATE_FORMAT.

returns 2007-11-30 10:30:19.000000

returns 2007 30th November 10:30:19 2007

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

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

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.

returns 2003-02-28

returns 2004-02-29

returns 2004-01-31

returns NULL

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

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

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

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

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.

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

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

Operand type: datetime is a DATE, TIME, or TIMESTAMP; str-interval is a character string

Result type: DATE, TIME, or TIMESTAMP

Returns datetime rounded up to the unit specified by str-interval. If str-interval is omitted, then datetime is rounded to the nearest day.

The str-interval can be any of the following:

Examples:

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

Operand type: str and format are character strings

Result type: DATE, TIME, or TIMESTAMP

Returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the format string contains only a date or time part. If the DATE, TIME, or DATETIME value extracted from string str is illegal, the function returns NULL and produces a warning. This function is the inverse of the DATE_FORMAT() function.

The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. For the specifiers that can be used in format, see the DATE_FORMAT.

returns

Result type: TIMESTAMP

Returns the current date and time set for the operating system on which the database resides. The format returned depends on the default TIMESTAMP format.

In distributed SQL statements, this function returns the date and time set for the operating system of your local database.

Examples:

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

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

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:

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:

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER

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

Operand types: date is a DATE, TIMESTAMP. The optional mode is an integer.

Result type: INTEGER2

Returns the week number for date.

If the mode parameter is omitted, 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 0 to 53 for the single parameter version. (Corresponds to mode 5 in the following table.)

The optional mode parameter lets you specify: the starting day of the week (Sunday or Monday); the definition of the first week of the year; and whether any days before the “first week of the year” are in week 0 or in the last week of the prior year.

The mode values are:

WEEK(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50

WEEK(DATE '2008-02-20',1) returns 8

WEEK(DATE '2008-12-31',1) returns 53

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

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

Operand types: date is a DATE or TIMESTAMP. The optional mode is an integer.

Result type: INTEGER4

Returns year and week for date.

The optional mode parameter lets you specify whether the week starts on Sunday or Monday.

Values for the optional mode parameter are shown under WEEK Function.

If the mode parameter is omitted, mode 0 is assumed (so that weeks start on Sunday, and week 1 is the first week with a Sunday; dates before the first Sunday of the year are assumed to be in the last week of the prior year).

For example: 1 January 2000 is a Saturday and is before the first week of the year, regardless of the mode selected. Therefore, it is treated as being in the last week of 1999. Since the last week of 1999 is week 52 (for all modes), YEARWEEK('2000-01-01',mode) returns 199952 (for all modes).

returns 198653

returns 200001

returns 199952

Operand type: CHAR, VARCHAR, ANSIDATE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

Result type: ANSIDATE

Converts the expression to internal ANSIDATE representation.

Operand type: CHAR, VARCHAR

Result type: BOOLEAN

Converts the string 'FALSE' and 'TRUE', without regard to case and where applicable, trailing or internal whitespace, to the corresponding BOOLEAN values.

Operand type: INTEGER1, SMALLINT, INTEGER, BIGINT

Result type: BOOLEAN

Converts the value 0 to FALSE and the value 1 to TRUE.

Operand type: Any

Result type: CHAR

Converts argument to char string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.

or

Operand type: Any except date and time types

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      Default
Data Type   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.

SELECT DECIMAL('12345678.999',14,2); returns 12345678.99

Operand type: Any absolute date

Result type: CHAR

Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL, INTEGER1, SMALLINT, INTEGER

Result type: FLOAT4

Converts the specified expression to FLOAT4. Numeric overflow can occur if the argument is too large for the result type (possible from string or decimal).

The range of values for float4 is processor dependent.

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER

Result type: FLOAT

Converts the specified expression to FLOAT. Numeric overflow can occur if the argument is too large for a float (possible from string or decimal).

The range of values for float is determined by IEEE_754.

Operand type: Any

Result type: VARCHAR

Returns the hexadecimal representation of the internal Vector 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.

HEX('ABC') returns '414243' (ASCII) or 'C1C2C3' (EBCDIC).

HEX(INT4(125)) returns '0000007D', the hexadecimal equivalent of the 4 byte binary integer 125.

or

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER

Result type: TINYINT

Converts the expression to TINYINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

or

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER

Result type: SMALLINT

Converts the expression to SMALLINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

or

or

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER

Result type: INTEGER

Converts the expression to INTEGER. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

or

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER

Result type: BIGINT

Converts the expression to BIGINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

Operand type: CHAR, VARCHAR, INTERVAL DAY TO SECOND

Result type: INTERVAL DAY TO SECOND

Converts the expression to internal INTERVAL DAY TO SECOND representation.

Operand type: CHAR, VARCHAR, INTERVAL YEAR TO MONTH

Result type: INTERVAL YEAR TO MONTH

Converts the expression to internal INTERVAL YEAR TO MONTH representation.

Operand type: CHAR, VARCHAR, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER

Result type: MONEY

Converts the expression to internal MONEY representation. Rounds floating point and decimal values, if necessary.

Operand type: Any

Result type: NCHAR

Converts argument to NCHAR Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using space characters.

Operand type: Any

Result type: NVARCHAR

Converts argument to NVARCHAR Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr.

or

Operand type: CHAR, VARCHAR, 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.

Operand type: CHAR, VARCHAR, 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.

Operand type: CHAR, VARCHAR, 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.

or

Operand type: CHAR, VARCHAR, 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.

Operand type: CHAR, VARCHAR, 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.

Operand type: CHAR, VARCHAR, 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.

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:

The format can be any of the following:

For TO_CHAR, the case and punctuation of the format are important. Formats whose results are alphabetic follow the case of the format. For example, 'DY' will yield 'MON' but 'Dy' will yield 'Mon'.

returns 2013-JULY-TUESDAY 23:42:00.290533 -07:00

returns 2013-JUL-30

returns 2013-07-30 23:36:19

Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR

Result type: ANSIDATE

Converts char to a value of ANSIDATE data type.

The format specifies the datetime format of char. When you omit format, then char must be in the default date format. Valid values for format are shown under TO_CHAR. The case and punctuation of the format are not important.

If any part of the date is omitted the first month or first month and first date is automatically added.

The following examples converts a character string into a date:

SELECT TO_DATE('2012-Dec-17', 'YYYY-MON-DD'); returns 2012-12-17.

SELECT TO_DATE('20121217', 'YYYYMMDD'); returns 2012-12-17.

SELECT TO_DATE('2012-Dec', 'YYYY-MON'); returns 2012-12-01.

SELECT TO_DATE('2012', 'YYYY'); returns 2012-01-01.

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 date:

returns 10:01:59

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 date:

returns 2012-12-17 00:00:00.000000

returns 2012-12-17 12:13:14.000000

returns 2012-12-01 00:00:00.000000

returns 2012-01-01 00:00:00.000000

returns 2013-10-11 20:01:59.123456

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 date:

returns 2012-12-17 00:00:00.000000-08:00

returns 2013-10-11 20:01:59.123456-04:00

Operand type: Any

Result type: VARCHAR

Converts argument to VARCHAR string. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.

Operand type: IPV4 or IPV6 addresses

Result type: Same as operands

Returns the logical AND of the two operands.

returns (as an IPv4)

Operand type: IPV4 or IPV6 addresses

Result type: Same as operand

Returns the logical NOT of the operand.

returns (as an IPv4):

Operand type: IPV4 or IPV6 addresses

Result type: Same as operands

Returns the logical OR of the two operands.

returns (as an IPv4):

Operand type: IPV4 or IPV6 addresses

Result type: Same as operands

Returns the logical XOR of the two operands.

returns (as an IPv4)

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.

returns the following single row:

Returns a random integer based on a seed value.

Returns a random float based on a seed value between 0 and 1. This is slower than RANDOM, but produces a more random number.

Returns a random integer within the specified range (that is, l <= x <= 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).

Operand types: Any

Result type: Any

Specifies a value other than a null that is returned to your application when a null is encountered. 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 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.

If the data type of both arguments to IFNULL function match, then the result will be that data type; if there is just a size difference then the larger will be used.

If the two arguments are of differing data types, they must be comparable. For a description of comparable data types, see Assignment Operations. This includes any collation type associated with the parameters; if differing collations are involved, a cast may be needed to make them compatible.

When the arguments are different but comparable data types, the following rules are used to determine the data type of the result:

Usually the first argument will be nullable but whether the function can return a null value depends only on the second argument; the result will only be nullable if a second argument is also nullable.

Operand types: Any

Result type: Derived from v1

Specifies a value other than a null that is returned to your application when a null is encountered. For example, in NVL(a,b), if 'a' is NULL then return 'b' else return 'a'.

The data type returned will be that of the first parameter, possibly with a length increase and made nullable if the second parameter is be nullable.

Operand types: Any

Result type: Derived from v2

Returns a different value based on whether the input value is NULL or not. For example, in NVL2(a,b,c), if 'a' is not null then return 'b' else return 'c'.

The data type returned will be that of the v2 parameter, possibly with a length increase for v3 and made nullable if either v2 or v3 parameter is nullable.

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.

returns

or

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.

returns the following (using hexadeximal representation):

Operand type: IPV4 or IPV6

Result type: Colon-hex string

Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase)

returns

or

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.

returns the following (using hexadecimal representation):