Description of Informix SQL Functions

Aggregate functions:
AVG, calculate the average value
Count, count the number of records
Max, calculate the maximum value
Min, minimum value
Range: calculates the difference between the maximum and minimum values of the selected row.
STDev: calculates the standard deviation of the selected row.
Sum, sum function
Variance: the variance of the sample returned by the function is used as the unbiased estimation of the variance of all selected rows.
Its formula, (sum (XI ** 2)-sum (xi) ** 2)/n)/(N-1)
XI indicates each value in the column, and N indicates the sum of values in the column.
Time functions:
The functions day, mdy, month, weekday, and year are returned with the values of the expressions or their own variables used to call the function. Current returns the current date and time value. 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 value with the current date.
The date function converts the string function to the date value. Example date ('2014/1/04 ')
The to_char function converts datetime and date values to character values.
The to_date function converts a character value to a datetime value. Example to_date ("1978-10-07", "% Y-% m-% d % H: % m)
Base function:
The cardinality (IDs only) function counts the number of elements contained in the set.
Intelligent Large Object functions (only applicable to IDS)
Filetoblob (), copy the file to the Blob Column
Filetoclob (), copy the file to the clob Column
Locopy () to copy blob or clob data to another blob or clob column.
Lotofile (), copy blob or clob to the file
String processing functions:
Lower, which converts each uppercase letter in the string to a lowercase letter.
Upper converts each lowercase letter in the string to an uppercase letter
Initcap, which converts the first letter of each word in the string to uppercase.
Replace: converts a group of characters in a string to other characters. For example, replace (COL, "each", "Eve ")
Substr: returns a part of the string, for example, substr (COL)
Substring, returns a part of the string, for example, substring (COL, from 1 to 4)
Lpad: Use the lpad function to repeat copies of a string that has been filled or truncated on the left, depending on the specified length of the filled part of the string.
For example, if the field col is of the char (15) type, select lpad (COL, 21, "_") from tab_name is displayed as adding six _ before Col _.
Rpad: Use the rpad function to repeat copies of strings that have been filled or truncated on the right of the string that have already been repeat for as many times as necessary, depending on the specified length of the padding part in the string.
For example, if the col field is of the char (15) type, select rpad (COL, 21, "_") from tab_name is displayed as adding six _ after Col _.
Other functions:
Hex, returns the hexadecimal number of the expression.
Round: returns the rounding value of the expression.
Trunc returns the truncation value of the expression.
Length: the length of the calculated expression.
User, returns the user name (account name) of the user performing the query)
Today, returns the current system date
Dbservername: the name of the database server, which is the same as sitename.
Dbinfo: returns information about the database.
Decode, function to convert an expression with one value to another
Decode (test, a, a_value, B, B _value, C, c_value ......), The decode function does not support the text and byte types.
Nvl to convert an expression with an empty value to another value.
You can also use stored procedures in select statements, such as select Spl ($ test) from tab_name