A function is a block of predefined code. Single-row functions return only a single row for each record processed. These functions include functions that deal with characters to dates.
| 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 |
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.
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:[0-9]{3}[.-][0-9]{3}[.-][0-9]{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. | |
DUAL Table
The DUAL table comes in handy if you want to play around and get to know functions. Using the DUAL table is as simple as using any other table.SELECT LENGTH("Hellow World!") FROM DUAL;