ORACLE - String Functions : Instr

The instr function returns the location of a substring in a string.
The syntax is:
instr( stringA, stringB [, start_position [, nth_appearance ] ] )
stringA is the string to search.
stringB is the substring to search for in stringA .
start_position is the position in stringA where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of stringA and then searches towards the beginning of stringA .
nth_appearance is the nth appearance of stringB. This is optional. If omitted, it defaults to 1.

For example:
instr('hello word hello', 'e') would return 2; the first occurrence of 'e'
instr('hello word hello', 'e', 1, 1) would return 2; the first occurrence of 'e'
instr('hello word hello', 'e', 1, 2) would return 13; the second occurrence of 'e'
instr('hello word hello', 'e', -3, 2) would return 2.
instr('hello word hello', 'X', 1, 1) would return 0.

ORACLE - Conversion Functions : To_char

the to_char function converts a number or date to a string.

The syntax :
to_char( value, [ format_mask ], [ nls_language ] )
value can either be a number or date that will be converted to a string.
format_mask is optional. This is the format that will be used to convert value to a string.
nls_language is optional. This is the nls language used to convert value to a string.

Examples - Numbers
The following are number examples for the to_char function.
to_char(1113214.23, '9999.999.9') would return '1113,214.7'
to_char(3214.23, '9,999.99') would return '3,214.23'
to_char(3214.23, '$9,999.00') would return '$3,214.23'
to_char(78, '000099') would return '000078'

Examples - Dates
The following are date examples for the to_char function.
to_char(sysdate, 'yyyy/mm/dd'); would return '2008/07/19'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 19, 2003'

ORACLE - Conversion Functions : To_Number

The to_number function converts a string to a number.

The syntax is:
to_number( string1, [ format_mask ], [ nls_language ] )
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.
nls_language is optional. This is the nls language used to convert string1 to a number.

For example:
to_number('981.53', 9999.99') would return the number 981.53
to_number('225', '999') would return the number 225
to_number('23', '99') would return the number 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows:
to_number('1210.73'') would return the number 1210.73

ORACLE - Date Functions : Months_Between

The months_between function returns the number of months between date1 and date2.

The syntax is:
months_between( dateA, dateB )
dateA and dateB are the dates used to calculate the number of months.
If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Example :
months_between (to_date ('2008/01/01', 'yyyy/mm/dd'), to_date ('2008/03/14', 'yyyy/mm/dd') )
would return -2.41935483870968
months_between (to_date ('2008/10/18', 'yyyy/mm/dd'), to_date ('2008/8/18', 'yyyy/mm/dd') )
would return 2

Applies To:
*Oracle 10g
*Oracle 11g