Gathering function:
AVG, seek average
Count, statistical records
Max, seeking maximum
Min, seeking minimum
Range, calculating the difference between the maximum value of the selected line and the minimum value
STDEV, calculating the standard deviation of the selected line
SUM, summary function
Variance, the variance of the function return value sample is a non-partial estimate of all variances of all options.
Its formula (SUM (xi ** 2) -Sum (xi) ** 2) / n) / (n-1)
Where XI is each value in the column, n is the sum of the column value.
Time function:
DAY, MDY, MONTH, WeekDay, Year These functions returns a value that is used to call the function or its own variable. Current Returns the current date and time value, you can use the extend function to adjust the accuracy of the Date or DateTime value.
Use the DAY and CURRENT functions to compare the column values with the current date.
The DATE function converts the string function to the DATE value. Example Date ('12 / 7/04 ')
The To_Char function converts the DATETIME and DATE values to a character value.
The to_date function translates the character value to the value of the DateTime type. Example To_date ("1978-10-07 10:00", "% Y-% M-% D% H:% M)
Benus function:
Cardinality (IDS only) function counts the number of elements included in the collection.
Intelligent large object function, (only to IDS)
Filetoblob (), copy files to the BLOB column
FileToClob (), copy file to the CLOB column
Locopy (), copy the data of the BLOB or Clob type to another blob or clob column
Lotofile (), copy BLOB or CLOB to the file
String handlers:
Lower, convert each uppercase letter in the string to lowercase letters
Upper, convert each lowercase letter in the string to uppercase letters
Initcap, convert the first letter of each word in the string to uppercase
Replace, convert a set of characters in the string into other characters, examples, replace (col, "each", "eve")
Substr, returns a part of the string, for example Substr (COL, 1, 2)
Substring, returns a part of the string, Substring (Col, from 1 to 4)
LPAD, using the LPAD function has reached the number of strings that have the necessary times to reach the number of strings on the left, depending on the specified length of the fill portion in the string.
Example: Field COL is a CHAR (15) type, SELECT LPAD (COL, 21, "_") from Tab_name is displayed as before colol, add six _.
RPAD, using the RPAD function has reached the number of characters of the necessary times to fill or truncated the number of strings on the right side, depending on the designation length of the fill portion in the string.
Example: Field COL is a CHAR (15) type, SELECT RPAD (COL, 21, "_") from Tab_name is displayed as six _.
Other functions:
HEX, return to the expression of the hexadecimal number
Round, returning to the expression of the four rounds
Trunc, returning the truncation value
Length, calculating the length of expression
User, returns the username of the user who performs queries (login account name)
Today, return to the current system date
DBSERVERNAME, return to the name of the database server, with SiteName
Dbinfo, return to the database
Decode, function to convert an expression with a value to another value
Decode (TEST, A, A_VALUE, B, B_VALUE, C, C_VALUE ...), the DECODE function does not support Text and Byte types. NVL to convert an expression of an empty expression into another value you want to specify.
You can also use a stored procedure in a SELECT statement, such as SELECT SPL ($ TEST) from Tab_Name
