A function is a block of predefined code. Singlerow functions return only a single row for each record processed. These functions include functions that deal with characters to dates.
When using a function in a SELECT the output will be modified accordingly but the data will not change in the database unless the function is used in an INSERT statement.
Type of Functions  Functions 
Case conversion  UPPER, LOWER, INITCAP 
Character manipulation  SUBSTR, INSTR, LENGTH, LPAD/RPAD, RTRIM/LTRIM, REPLACE, TRANSLATE, CONCAT 
Numeric  ROUND, TRUNC, MOD, ABS 
Date  MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, TO_DATE, ROUND, CURRENT_DATE 
Regular expressions  REGEXP_LIKE, REGEXP_SUBSTR 
Other  NVL, NVL2, TO_CHAR, DECODE, SOUNDEX, TO_NUMBER 
LOWER(c) 

Parameters:  c  Value to convert to lower case. 
Description  
Will take a string or column and convert the data into all lower case. 
UPPER(c) 

Parameters:  c  Value to convert to upper case. 
Description  
Converts the string to all uppercase. 
INITCAP(c) 

Parameters:  c  Value to convert the first letter to upper case. 
Description  
This function is used to make strings more readable during output. The first character is converted into uppercase. 
SUBSTR(c, p, l) 

Parameters:  c  The string to look in. p  The position to begin extracting characters. l  The number of characters to extract from the string. 
Description  
The SUBSTR takes a string and from p (being the starting position) will extract the number of characters defined by l. The p can be a negative number which will count the characters from the right. Examples of this function are:SUBSTR('abcdefg', 2, 3)will return: bcd'SUBSTR('abcdefg', 4, 3)will return: 'def' 
INSTR(c, i, [p], [n]) 

Parameters:  c  String to look in. i  What to look for. p  Where to start looking. (optional) n  location of n instance. (optional) 
Description  
This function will look in another string for a specified string. If the function does not find anything then the function will return 0 otherwise it will return the location of the string it found.INSTR('abcdefg', 'c')Will return 3 because c is located in the third position in the string. INSTR('abcdefabc', 'abc', 2)Will return 7 because 'abc' starts at position 7 and is the second instance of abc. INSTR('abcdefabc', 'bc', 1, 2)Returns 8 because Oracle has located the first 'bc' after the second occcurance. 
LENGTH(c) 

Parameters:  c  Value to convert to retrieve the length from. 
Description  
Counts the number of characters in the string and returns it.In passing a column through this function with a data type of CHAR, it will always pass the maximum size of the column. 
LPAD(c,l,s) & RPAD(c,l,s) 

Parameters:  c  string to be padded l  total length of the string after being padded. s  character to use as padding. 
Description  
These functions will take a string and will pad it with a character. The l indicates that you want the resulting string to be that long. Whatever space isn't filled by the string (c) will be filled in with the padding character (s) so the end result will be so many characters long defined by l. The LPAD will pad the string from left to right where the RPAD will pad the string from right to left. 
LTRIM(c,s) & RTRIM(c,s) 

Parameters:  c  String to be modified. s  String to be removed from c 
Description  
The trim functions will remove a specific string defined by the user. Depending on which TRIM function is used it will either remove the string defined in 's' from the left if the LTRIM is used or the right if RTRIM is used. 
REPLACE(c, s, r) 

Parameters:  c  data to be searched through s  String to look for r  what to replace the string with. 
Description  
The REPLACE function will look for s in c. For each occurance it will replace s with r. 
TRANSLATE 

Parameters:  c  Value to convert to look in. c  Character to look for. s  Character to replace with. 
Description  
The TRANSLATE function is different from the REPLACE function because it can only handle one character at a time rather than a string. For Example:TRANSLATE('abcdefg', 'd', ',')This replaces 'd' with a comma ('abc,efg'). The TRANSLATE function can also be used like this:TRANSLATE('abcdefg', 'df', ',')When TRANSLATE is used like this, the function will replace all instances of 'd' with a comma and all instances of 'f' with a dash. 
CONCAT(c1,c2) 

Parameters:  c1  First column to concatenate. c2  Second column to concatenate. 
Description  
The concatenate function will take two columns and combine them. The difference between this function and the '' is that the CONCAT fucntion can only handle two columns or strings where the concatenate operator can combine as many as you want. 
ROUND(n, p) or ROUND(d,f) 

Parameters:  n  Number to round. p  Percision to round to. d  date to round. f  how to round the data. 
Description  
The ROUND function will round a number depending on the percision that is provided. The percision can be 1,2,3,.. Positive numbers count the places to the right of the decmial point to round. If a negative number is used then Oracle counts from to the left of the decimal point.ROUND(19.9283, 3) ROUND(19.9283, 2) ROUND(19.9283, 1)The values returned are 19.928, 19.93, and 20, respectfully. The round function can also be apply to dates. The first parameter is the date that is to be rounded. The second parameter is how the date will be rounded. 
TRUNC(n, p) 

Parameters:  n  Number to truncate p  position at which to truncate the number. 
Description  
After the point defined in p, the numbers are dropped. If p is a negative number then the function will drop everything to the left of the decimal point. Anything being dropped to the left of the decimal point will become zero. 
MOD 

Parameters:  n  the number to get the remainder from. r  The remainder of 
Description  
When dividing numbers, they may not always come out nice. In the event that there is remained the MOD function will easily reveal it.MOD(25/9)Will return 7 as there is 7/9 left over from dividing 25 by 9. 
ABS(n) 

Parameters:  n  The number to get the absolute value. 
Description  
ABS returns the absolute value of n. 
MONTHS_BETWEEN(d1, d2) 

Parameters:  d1  first date to compare d2  Second date to compare. 
Description  
Finds the number months between d1 and d2. 
ADD_MONTHS(d,n) 

Parameters:  d  date to add months to. n  Number of months to add. 
Description  
ADD_MONTHS simply adds the number of months that are specified. 
NEXT_DAY(d,day) 

Parameters:  d  date to get the next day for. day  Next day from d. 
Description  
NEXT_DAYdetermines the next occurence of a specific day of the week after a given date. 
TO_DATE(d, f) 

Parameters:  d  Date being entered f  format of the date that's being entered.  
Description  
The TO_DATE function comes in handy for developers. d is a string that is to be converted into a date object. The f is how the d is formatted. The format elements are listed below:

REGEXP_LIKE(d,p) 

Parameters:  d  data to search through. p  pattern to use for searching. 
Description  
The REGEXP_LIKE uses a string unlike what you would use in a WHERE clause with LIKE. The pattern is in the format of []{}. An Example of a pattern that would be used to search for phone numbers:[09]{3}[.][09]{3}[.][09]{4}The contents of the square brackets are what to look for. The number in the curley brackets defines how many places to look in. If there is no curley brackets following the square brackets then it's assumed you're only looking for one instance. 
NVL(d, s) 

Parameters:  d  Column to check against NULL data. s  Value to substitude if d is NULL. 
Description  
When using a NULL value in a calculation the result in a NULL. In the event that d is NULL the value defined in s will be used instead of the NULL. 
NVL2(d,s1,s2) 

Parameters:  d  Column to check for NULL. s1  What should be substituded if d is not NULL. s2  What should be substituded if d is NULL. 
Description  
The NVL2 works the same way but adds one extra parameter. This function adds the ablity to substitude a value even if there is a value. 
TO_CHAR(d, f) 

Parameters:  d  Data to be formatted. f  Format to use.  
Description  
The TO_CHAR works the same way as the TO_DATE. The d is the data that is to be formatted by f.

DECODE(v, l1, r1, l2, r2, ...) 

Parameters:  v  is the value you are searching for. l1  first value that is found. r1  first result to return when the first value is found. l2  second value that is found. r2  second result to return when the second value is found. 
Description  
The DECODE function will take a value and compare it to a list of values. When a match is found it will return the result that is associated with it.SELECT DECODE(state, 'CA', .08, 'WI', .05, 0) "Tax Rate" FROM DUALCA  .08 WI  .05 TX  0 
SOUNDEX(c) 

Parameters:  c  String to search for phonetic pronunciations. 
Description  
SOUNDEX will take a value and comare it to all the known phonetic equivelents. If we passed the word "smyth" through the SOUNDEX function we would get "smith" 
TO_NUMBER(d) 

Parameters:  d  the string that is to be converted into a number. 
Description  
The TO_NUMBER function makes it possible to take strings that contain numbers such as "2007" and convert it into something that can be used in a calculation. If the string contains nonnumeric numbers the function will error out. 