Informix SQL function Rollup

Aggregate functions

AVG, average

count, number of bars in the statistics record

Max, find the maximum value

Min, find the minimum value

Range, which calculates the difference between the maximum and minimum values of the selected row

STDEV, calculates the standard deviation of the selected line

Sum, Sum function

Variance, the variance of the function return value sample is an unbiased estimate of the variance of all selected rows. Its formula, (SUM (xi**2)-sum (xi) **2)/N)/(N-1)

Where Xi is each value in the column, and N is the sum of the values in the column.

Time function

Day,mdy,month,weekday,year These functions return the value of the expression or its own variable that is used to invoke the function. Current returns the present date and time value, and you can use the Extend function to adjust the precision of the date or datetime value. Use the day and current functions to compare the column values to the present date.

The date function converts a string function to a date value. Example date (' 12/7/04 ')

The To_char function converts datetime and date values to character values.

The To_date function converts a character value to a value of datetime type. Example To_date ("1978-10-0710:00", "%y-%m-%d%h:%m")

cardinality function

Cardinality (IDs only) function counts the number of elements that the collection contains.

Intelligent large Object function (only applicable with IDs)

Filetoblob (), copying files to a BLOB column

Filetoclob (), copying files to the CLOB column

Locopy (), copy blob or CLOB type of data to another BLOB or CLOB column

Lotofile (), copying a blob or CLOB to a file

String handler function

Lower, converts each uppercase letter in a string to a lowercase letter

Upper, converts each lowercase letter in a string to uppercase

Initcap, converts the first letter of each word in the string to uppercase

Replace, converts a set of characters from a string to another character, example replace (col, "Each", "Eve")

SUBSTR, returns a part of the string, example substr (col,1,2)

SUBSTRING, returns a part of the string, example substring (col,from1to4)

Lpad, a copy of a string that is filled or truncated on the left by using the Lpad function to reach the necessary number of repetitions, depending on the specified length of the fill part in the string.

For example: Field col is char (15) type, Selectlpad (col,21, "_") Fromtab_name is displayed as six _ before Col.

Rpad a copy of a string that is filled or truncated on the right with a sequence of characters that the Rpad function has used to reach the necessary number of times, depending on the specified length of the fill part in the string.

For example: Field col is char (15) type, Selectrpad (col,21, "_") Fromtab_name is shown to add six _ after Col.