Oracle function list in alphabetical order
| Name | Description |
|---|---|
| ABS(x) | absolute value of x |
| ACOS(n) | Arccosine |
| ADD_MONTHS(d, n) | Date d plus n months |
| ASCII | Gives the ASCII value of the first character of a string. |
| ASCIISTR | Converts x to an ASCII string, where x may be a string in any character set. |
| ASIN(n) | Arcsine of n |
| ATAN(n) | Arctangent of n |
| ATAN2(x,y) | Returns the arctangent of x and y. |
| AVG | Calculates the average |
| BIN_TO_NUM | Converts a binary number x to a NUMBER. |
| BITAND(x,y) | a bitwise AND on x and y. |
| CAST(x AS type) | Converts x to a compatible type specified in type. |
| CEIL | Returns the ceiling value (next highest integer above a number). |
| CHARTOROWID(x) | Converts x to a ROWID. |
| CHR(n) | Character with ASCII value n |
| COALESCE(a, b, ...) | The first not NULL argument (and NULL if all arguments are NULL) |
| COMPOSE(x) | Converts x to a Unicode string in normalized form. |
| CONCAT | Concatenates two strings |
| CONVERT(x,source_char_set,dest_char_set) | Converts x from source_char_set to dest_char_set. |
| COS(n) | Cosine of n (n expressed in radians) |
| COSH(n) | Hyperbolic cosine of n |
| COUNT(x) | Returns the number of rows returned by a query involving x |
| CUBE | Returns rows containing a subtotal for all combinations of columns |
| CUME_DIST() | Returns the position of a value relative to a group. |
| CURRENT_DATE | returns the current date in the local time zone in the database session |
| CURRENT_TIMESTAMP | Returns a TIMESTAMP WITH TIME ZONE for the session |
| DBTIMEZONE | Returns the time zone for the database |
| DECODE | Use it as if then else statement |
| DECOMPOSE(x) | Decomposes the string into the same character set as x and convert x to a Unicode string. |
| DENSE_RANK | Rank the rows and does not skip ties |
| EXP | Returns e raised to a value. |
| EXTRACT | Extracts and returns the year, month, day, hour, minute, second, or time zone from x. |
| EXTRACT(c FROM d) | Extract date/time component c from expression d |
| FIRSTFirst | gets the first values in an ordered group. |
| First_value | Gets the first rows in a window. |
| FLOOR | Returns the floor value (next lowest integer below number). |
| FROM_TZ(x, time_zone) | Merges xand time_zone into one value. |
| GREATEST(a, b, ...) | Greatest value of the function arguments |
| GROUP_ID | Removes duplicate rows returned by a ROLLUP or CUBE. |
| GROUPING | Deals with NULL value for ROLLUP and CUBE |
| GROUPING_ID | Returns the decimal equivalent of the GROUPING bit vector. |
| HEXTORAW(x) | Converts the character x containing hexadecimal digits (base-16) to a binary number (RAW). |
| Hypothetical | Rank and Distribution Functions |
| INITCAP | Changes the first letter of a string or series of words into uppercase. |
| INSTR | Returns the beginning location of a pattern in a given string. |
| LAG | |
| LAST | gets the last values in an ordered group. |
| LAST_DAY(d) | Last day of the month containing date d |
| Last_value | Gets the last rows in a window. |
| LEAST(a, b, ...) | Least value of the function arguments |
| LENGTH | Returns the length of a string. |
| LN(n),LOG(m,n) | Natural logarithm, and logarithm base m |
| LOCALTIMESTAMP | Returns a TIMESTAMP for the session. |
| LOWER | Converts every letter of a string to lowercase. |
| LPAD | Adds a specified set of characters to the left of the original string. |
| LTRIM | Removes a set of characters from the left of a string. |
| MAX | Returns the maximum value |
| MEDIAN | Returns the median value |
| MIN | Returns the minimum value |
| Mod | Returns The Remainder Of N/M Where Both N And M Are Integers. |
| MONTHS_BETWEEN(d, e) | Months between dates d and e |
| NEW_TIME(x,time_zone1,time_zone2) | Converts x from time_zone1 to time_zone2 |
| NEW_TIME(d, z1, z2) | Convert date/time d from time zone z1 to z2 |
| NEXT_DAY(d, weekday) | The first weekday (mon, tue, etc.) after d |
| NTILE | Groups data by putting data into a number of percentile groups |
| NULLIF(a, b) | NULL if a = b; otherwise a |
| NUMTODSINTERVAL(x) | Converts the number x to an INTERVAL DAY TO SECOND. |
| NumToDSInterval | Converts number x to INTERVAL DAY TO SECOND. |
| NUMTOYMINTERVAL(x) | Converts the number x to an INTERVAL YEAR TO MONTH. |
| NumToYMInterval | Converts number x to INTERVAL YEAR TO MONTH. |
| NVL | Returns value if x is null; otherwise x is returned. |
| NVL2 | Returns value1 if x is not null; otherwise value2 is returned. |
| PARTITION BY | Separate data groups and perform calculation within that group |
| PERCENT_RANK() | Returns the percent rank of a value relative to a group. |
| PERCENTILE_CONT | Checks the percent rank values in each group |
| PERCENTILE_DISC | Checks the cumulative distribution values |
| POWER | Returns value raised to some exponential power. |
| RANK | Returns the rank of items in a group and leaves a gap for a tie. |
| RATIO_TO_REPORT() | Ratio to report |
| RAWTOHEX(x) | Converts the binary number (RAW) x to a VARCHAR2 string in equivalent hexadecimal number. |
| RAWTONHEX(x) | Converts the binary number (RAW) x to an NVARCHAR2 string in the equivalent hexadecimal number. |
| REGEXP_COUNT | Returns the number of times the pattern is found. |
| REGEXP_INSTR | Returns the location of a pattern in a given string and supports the regular expression. |
| REGEXP_LIKE | Searches x for the regular expression defined in the pattern parameter. |
| REGEXP_REPLACE | Replaces string with the regular expression matching supports |
| REGEXP_SUBSTR | Returns the substring with the regular expression matching supports |
| REGR_AVGX(y, x) | Returns the average of x after eliminating x and y pairs |
| REGR_AVGY(y, x) | Returns the average of y after eliminating x and y pairs |
| REGR_COUNT(y, x) | Returns the number of non-null number pairs that are used to fit the regression line |
| REGR_INTERCEPT(y, x) | Returns the intercept on the y-axis of the regression line |
| REGR_R2(y, x) | Returns the coefficient of determination of the regression line |
| REGR_SLOPE(y, x) | Returns the slope of the regression line |
| REGR_SXX(y, x) | Returns REG_COUNT (y, x) * VAR_POP(x) |
| REGR_SXY(y, x) | Returns REG_COUNT (y, x) * COVAR_POP(y, x) |
| REGR_SYY(y, x) | Returns REG_COUNT (y, x) * VAR_POP (y) |
| REMAINDER(n1, n2) | Identifies the multiple of n2 that is nearest to n1, and returns the difference between those two values. |
| REPLACE | Does the string replacement |
| ROLLUP | Returns a row containing a subtotal |
| ROUND(d[, fmt]) | d rounded on fmt (the default for fmt is midnight) |
| ROUND | Returns the number rounded to nearest value, adjusts precision. |
| ROW_NUMBER | row number for ranking |
| ROWIDTOCHAR(x) | Converts the ROWID x to a VARCHAR2 string. |
| ROWIDTONCHAR(x) | Converts the ROWID x to an NVARCHAR2 string. |
| ROWNUM | pseudo-column and is calculated as rows are retrieved |
| RPAD | Adds a specified set of characters to the right of the original string. |
| RTRIM | Removes a set of characters from the right of a string. |
| SESSIONTIMEZONE | Returns the database session time zone |
| Sign | Returns 1 If The Argument Is Positive; -1 If The Argument Is Negative; And 0 If The Argument Is Negative. |
| SIN(n) | Sine of n (n expressed in radians) |
| SINH(n) | Hyperbolic sine of n |
| SOUNDEX | Converts a string to a code value based on similar sounds. |
| Sqrt(x) | Square Root Of x |
| STDDEV | Returns the standard deviation |
| String||String | Concatenates two strings. |
| SUBSTR | Retrieves a portion. |
| SUM | Returns the sum |
| SYS_EXTRACT_UTC(x) | Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP in UTC. |
| SYS_EXTRACT_UTC | Extracts the UTC from a datetime value. |
| Sysdate | Returns the current operating system date from database server |
| SYSTIMESTAMP | Returns a TIMESTAMP WITH TIME ZONE for the database. |
| TAN(n) | Tangent of n (n expressed in radians) |
| TANH(n) | Hyperbolic tangent of n |
| TO_BINARY_DOUBLE(x) | Converts x to a BINARY_DOUBLE. |
| TO_BINARY_FLOAT(x) | Converts x to a BINARY_FLOAT. |
| TO_BLOB(x) | Converts x to a binary large object (BLOB). |
| TO_CHAR | Converts number and date value to string and format |
| TO_CLOB(x) | Converts x to a character large object (CLOB). A CLOB is used to store large amounts of character data. |
| TO_DATE | Converts string to date value |
| TO_DSINTERVAL(x) | Converts the string x to an INTERVAL DAY TO SECOND. |
| TO_DSInterval | Converts string x to INTERVAL DAY TO SECOND. |
| TO_MULTI_BYTE(x) | Converts the single-byte characters in x to the corresponding multi-byte characters. |
| TO_NCHAR(x) | Converts x in the database character set to an NVARCHAR2 string. |
| TO_NCLOB(x) | Converts x to a large object NCLOB. |
| TO_NUMBER(x [, format] | Converts x to a NUMBER. |
| TO_SINGLE_BYTE(x) | Converts the multi-byte characters in x to the corresponding single-byte characters. |
| TO_TIMESTAMP(x) | Converts the string x to a TIMESTAMP. |
| TO_TIMESTAMP(x, [format]) | Converts the string x to a TIMESTAMP. |
| TO_TIMESTAMP_TZ(x) | Converts the string xto a TIMESTAMP WITH TIME ZONE. |
| TO_TIMESTAMP_TZ(x, [format]) | Converts the string x to a TIMESTAMP WITH TIMEZONE. |
| TO_YMINTERVAL(x) | Converts the string x to an INTERVAL YEAR TO MONTH. |
| TO_YMInterval | Converts string x to INTERVAL YEAR TO MONTH. |
| TRANSLATE(x,from_string, to_string) | Converts all occurrences of from_string in x to to_string. |
| TRANSLATE | Replaces a string character by character. |
| TRIM | Removes a set of characters from both sides. |
| TRUNC(d[, fmt]) | d truncated on fmt (the default for fmt is midnight) |
| TRUNC | Returns the truncated value (removes decimal part of a number, precision adjustable). |
| TZ_OFFSET(time_zone) | Returns the offset for time_zone in hours and minutes |
| UNISTR(x) | Converts the characters in x to an NCHAR character. |
| UPPER | Converts every letter to uppercase. |
| VARIANCE | Returns the variance |
| VSIZE | Returns the storage size. |