Oracle provides a comprehensive set of built-in functions categorized into various types. Here’s a list of the commonly used categories and functions:
1. String Functions
LOWER(char)– Converts to lowercaseUPPER(char)– Converts to uppercaseINITCAP(char)– Capitalizes the first letterLENGTH(char)– Returns length of a stringSUBSTR(char, start, length)– Extracts substringINSTR(char, substring)– Finds position of substringTRIM(char)– Removes leading/trailing charactersLPAD(char, length, pad_char)– Left padRPAD(char, length, pad_char)– Right padREPLACE(char, search, replace)– Replaces textCONCAT(char1, char2)– Concatenates strings
2. Numeric Functions
ABS(number)– Absolute valueROUND(number, decimal_places)– Rounds to n decimal placesTRUNC(number, decimal_places)– Truncates to n decimal placesMOD(number1, number2)– Remainder of divisionCEIL(number)– Rounds up to next whole numberFLOOR(number)– Rounds down to previous whole numberPOWER(base, exponent)– Raises to the powerSQRT(number)– Square rootSIGN(number)– Returns -1, 0, or 1
3. Date Functions
SYSDATE– Returns current date and timeCURRENT_DATE– Date in current session timezoneADD_MONTHS(date, number)– Adds months to dateLAST_DAY(date)– Last day of the monthMONTHS_BETWEEN(date1, date2)– Difference in monthsNEXT_DAY(date, weekday)– Next specified weekdayROUND(date, format)– Rounds date to the nearest unitTRUNC(date, format)– Truncates date to specified unit
4. Conversion Functions
TO_CHAR(date_or_number, format)– Converts to stringTO_DATE(char, format)– Converts to dateTO_NUMBER(char)– Converts to number
5. Aggregate Functions
AVG(column)– AverageCOUNT(column)– Number of rowsSUM(column)– SumMAX(column)– MaximumMIN(column)– MinimumSTDDEV(column)– Standard deviationVARIANCE(column)– Variance
6. Conditional Functions
CASE WHEN condition THEN result ELSE result END– Conditional logicDECODE(expr, search, result, default)– Compares valuesNVL(expr1, expr2)– Replace NULLNVL2(expr1, expr2, expr3)– Replace based on NULL checkNULLIF(expr1, expr2)– Returns NULL if equal
7. Miscellaneous Functions
USER– Returns the current userUID– Returns user IDSYS_CONTEXT(namespace, parameter)– Returns environment contextROWNUM– Row number in result setROWID– Unique row identifier
This list covers most of the built-in functions you will encounter in Oracle SQL. Each function plays a specific role in manipulating or retrieving data effectively.
