Oracle provides single row functions to manipulate the data values. The single row functions operate on single rows and return only one result per row. In general, the functions take one or more inputs as arguments and return a single value as output. The arguments can be a user-supplied constant, variable, column name and an expression.
Act on each row returned in the query.
Manipulate the output for groups of rows.
Returns one result per row.
Used in SELECT, WHERE and ORDER BY clauses.
Single row functions can be nested.
Character Functions: Character functions accept character inputs and can return either character or number values as output.
Number Functions: Number functions accepts numeric inputs and returns only numeric values as output.
Date Functions: Date functions operate on date data type and returns a date value or numeric value.
Conversions Functions: Converts from one data type to another data type.
The Lower function converts the character values into lowercase letters.
The Upper function converts the character values into uppercase letters.
The Initcap function coverts the first character of each word into uppercase and the remaining characters into lowercase.
The Concat function coverts the first string with the second string.
The Substr function returns specified characters from character value starting at position m and n characters long. If you omit n, all characters starting from position m to the end are returned.
You can specify m value as negative. In this case the count starts from the end of the string.
The Length function is used to find the number of characters in a string.
The Instr function is used to find the position of a string in another string. Optionally you can provide position m to start searching for the string and the occurrence n of the string. By default m and n are 1 which means to start the search at the beginning of the search and the first occurrence.
The Lpad function pads the character value right-justified to a total width of n character positions.
The Rpad function pads the character value left-justified to a total width of n character positions.
The Trim function removes the leading or trailing or both the characters from a string.
The Replace function is used to replace a character with another character in a string.
The Round function rounds the value to the n decimal values. If n is not specified, there won't be any decimal places. If n is negative, numbers to the left of the decimal point are rounded.
The Trunc function truncates the value to the n decimal places. If n is omitted, then n defaults to zero.
The Mod function returns the remainder of m divided by n.
The Sysdate function returns the current oracle database server date and time.
SELECT sysdate-hire_date "sub_dates" FROM EMPLOYEES; -- returns number of days between the two dates.
The Months_Between function returns the number of months between the two given dates.
The Add_Months is used to add or subtract the number of calendar months to the given date.
The char can be a character string or a number representing the day.
The Last_Day function returns the last day of the month.
The Oracle Conversion and General Functions are covered in other sections. Go through the links Oracle Conversion Functions and Oracle General Functions.