Selected Single-Row Functions

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:    
Date Formats
Element Description Example
MONTH Full name of the month and padded with blank spaces to a toal width of nine spaces. APRIL
MON Three letter abbreviation for the name of the month. APR
MM Two digit number of the month. 04
RM Roman numeral month. IV
D Numeric value of the day of the week. Wednesday = 4
DD Numeric value for the day of the month. 07
DDD Numeric value for the day of the year. December 31 = 365
DAY Name of day. Saturday
DY Three letter abbreviation for the day of the week. SAT
YYYY or YYY or YY or Y Four-digit, last three, last two, and last one digit numeric value of the year. 2007, 007, 07, 7
YEAR Spelled out equivelent of the year. TWO THOUSAND SEVEN
B.C. or A.D. Value indicating B.C. or A.D. 2007 A.D.

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.    
Date Formats
Element Description Example
MONTH Full name of the month and padded with blank spaces to a toal width of nine spaces. APRIL
MON Three letter abbreviation for the name of the month. APR
MM Two digit number of the month. 04
RM Roman numeral month. IV
D Numeric value of the day of the week. Wednesday = 4
DD Numeric value for the day of the month. 07
DDD Numeric value for the day of the year. December 31 = 365
DAY Name of day. Saturday
DY Three letter abbreviation for the day of the week. SAT
YYYY or YYY or YY or Y Four-digit, last three, last two, and last one digit numeric value of the year. 2007, 007, 07, 7
YEAR Spelled out equivelent of the year. TWO THOUSAND SEVEN
B.C. or A.D. Value indicating B.C. or A.D. 2007 A.D.
Time Formats
SS Seconds 0-59
SSSS Seconds past midnight. 0-86399
MI Minutes 0-59
HH or HH12 Hours in 12 hour format 1-12
HH24 Hours in 24 hour format 0-23
A.M. or P.M. Value indicating morning or evening hours. A.M. before noon or P.M. after noon
Number
9 Indicates width to display with a series of 9s. 99999
0 Displays insignificant leading zeros 00999
$ Displays a floading dollar sign. $99999
. Indicates number of decimals to display 999.99
, Displays a comma in the position indicated 9,999
Other
., (punctuation symbols) Displays indicated punctuation DD,YYYY = 24, 2007
"string" Displays exact character string inside the double quotes "of year" YYYY = of year 2007
TH displays the ordinal number DDTH = 8th
SP spells out the number DDSP = eight
SPTH Spell out the ordinal number eighth

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;