Conversion functions are very useful while working with SQL or PL/SQL. These functions are used for data type conversions. These functions play big role in writing SQL queries and PL/SQL blocks.
We have two types of data type conversions.
1) Implicit Data Type Conversion
2) Explicit Data Type Conversion
Implicit Data Type Conversion ::
Implicit data type conversion is done by Oracle server. The data type conversion which is done implicitly by the Oracle server is called as Implicit Data Type Conversion.
For assignments (means assigning values to variables) Oracle server can automatically do following conversions.
VARCHAR2 or CHAR ---> NUMBER
VARCHAR2 or CHAR ---> DATE
NUMBER ---> VARCHAR2
DATE ---> VARCHAR2
These are the conversions which will be done by Oracle server automatically when doing assignment operations.
For Ex,
If we have declared a variable x of Varchar2 data type and if we assign value 4 (Number) to it as shown below,
x =4 ;
Then Server will store this value as character value ('4') because of implicit conversion.
Note : For this implicit conversion to be successful valid value should be there. It means we cannot convert 'ab' to number as it did not contain valid number. We can convert '44' to number as it contain valid number.
For expression evaluation (means calculations involving arithmetic operations) Oracle server can automatically convert the following.
VARCHAR2 or CHAR ---> NUMBER
VARCHAR2 or CHAR ---> DATE
Note : CHAR to NUMBER conversions will succeed only if character string represents a valid number.
Although Implicit conversion available it is better to go for explicit data type conversion for reliability and better performance.
Explicit Data Type Conversion ::
The data type conversion which is done by users explicitly will be called as Explicit Data Type conversion. We will use data type conversion functions for doing Explicit conversion.
There are 3 explicit data type functions available in SQL.
1) To_Char
2) To_Date
3) To_Number
To_Char Function ::
This function is used to convert the data of NUMBER or DATE data type to VARCHAR2 data type.
SYNTAX :: To_Char (Number or Date, Format)
Here Number or Date refers to the Number or Date data which we want to convert to the Character data.
Format refers to the output format to which we want to convert date or Number data.
Here format will contain valid format models of Number or Date conversions. We have many format models for Numbers and Dates. Here I will give some important ones which will be frequently used in conversions.
For Number conversions format models can be used are,
$ - Dollar symbol used for representing the money
L - Local currency symbol will be shown if used
9 - Used as replacement for digits in the input number
0 - Used to place 0's in the output
, - comma seperator
. - used in decimal point numbers
For Ex,
Let us say we want to convert number 44444.44 to character string like '$44,444.44' then we
can use following,
SELECT To_Char (44444.44, '$99,999.99') From Dual;
This will give output as $44,444,44. So, this is how we can convert Numbers into Character Data
using format models.
For Date conversions format models can be used are,
MM - 2 digit month (Ex: 20)
MON - 3 character month (Ex: 'JUN')
Month - Full name of Month (Ex: 'JUNE')
DD - 2 digit date (Ex: 11)
DY - 3 character day (Ex : 'MON')
DAY - Full name of Day (Ex: 'MONDAY')
YY - 2 digit year (Ex: 99)
YYYY - 4 digit year (Ex: 1999)
Year - year spelled out (Ex: Nineteen Ninty Nine)
MI - Minutes
SS - Seconds
HH12 - Hours in 12hr format
HH24 - Hours in 24hr format
fm - can be used to suppress leading 0's in day of input date
(Ex: 09 will be shown as 9)
sp - used to spell out the day in input date (Ex: seven)
th - used to add th or nd or rd in output while spelling out day in input date
-, . , / - separators used to separate day, month and year in output
Ex,
Select To_char ('14-jul-2012', 'fmddspth "of" Month YYYY') From dual;
This will give output as 14th of July 2012. we can use "of" also in output string as shown.
While using this function we must keep in mind that the format string which we will provide to the function will be that of in which format we want the output to be displayed.
So, this is how we can convert Numeric or Date data into character data using To_Char function.
To_Number and To_Date Functions ::
To_Number function is used to convert character data to Numeric data. To_Date function is used to convert character data to date data. In order to convert Character data to Numeric or Date data input character data must contain valid Numbers or dates respectively.
Syntax :: To_Number (i/p char, format)
To_Date (i/p char, format)
In both syntax i/p char refers to the input character data which we want to convert to numeric data or Date data.
format refers to the format in which we are providing input character data .
Here also we use format models. These are same as which we have discussed in To_Char function.
Ex : SELECT To_Number ('$46,567.67', '$99,999.99') From Dual;
This will give output as 46567.67.
SELECT To_Date ('19/04/1999','dd/mm/yyyy') From Dual;
This will give output as '19-Apr-1999' (Remember this is default and valid format of dates in SQL).
This is how we can convert Character data to Numeric or Date data.
Note : The most important point to keep in mind while working with To_Char, To_Number and To_Date functions is,
--> In To_Char function the format we will provide is of output.
--> In To_Date and To_Number functions format we will provide is of input.
We have two types of data type conversions.
1) Implicit Data Type Conversion
2) Explicit Data Type Conversion
Implicit Data Type Conversion ::
Implicit data type conversion is done by Oracle server. The data type conversion which is done implicitly by the Oracle server is called as Implicit Data Type Conversion.
For assignments (means assigning values to variables) Oracle server can automatically do following conversions.
VARCHAR2 or CHAR ---> NUMBER
VARCHAR2 or CHAR ---> DATE
NUMBER ---> VARCHAR2
DATE ---> VARCHAR2
These are the conversions which will be done by Oracle server automatically when doing assignment operations.
For Ex,
If we have declared a variable x of Varchar2 data type and if we assign value 4 (Number) to it as shown below,
x =4 ;
Then Server will store this value as character value ('4') because of implicit conversion.
Note : For this implicit conversion to be successful valid value should be there. It means we cannot convert 'ab' to number as it did not contain valid number. We can convert '44' to number as it contain valid number.
For expression evaluation (means calculations involving arithmetic operations) Oracle server can automatically convert the following.
VARCHAR2 or CHAR ---> NUMBER
VARCHAR2 or CHAR ---> DATE
Note : CHAR to NUMBER conversions will succeed only if character string represents a valid number.
Although Implicit conversion available it is better to go for explicit data type conversion for reliability and better performance.
Explicit Data Type Conversion ::
The data type conversion which is done by users explicitly will be called as Explicit Data Type conversion. We will use data type conversion functions for doing Explicit conversion.
There are 3 explicit data type functions available in SQL.
1) To_Char
2) To_Date
3) To_Number
To_Char Function ::
This function is used to convert the data of NUMBER or DATE data type to VARCHAR2 data type.
SYNTAX :: To_Char (Number or Date, Format)
Here Number or Date refers to the Number or Date data which we want to convert to the Character data.
Format refers to the output format to which we want to convert date or Number data.
Here format will contain valid format models of Number or Date conversions. We have many format models for Numbers and Dates. Here I will give some important ones which will be frequently used in conversions.
For Number conversions format models can be used are,
$ - Dollar symbol used for representing the money
L - Local currency symbol will be shown if used
9 - Used as replacement for digits in the input number
0 - Used to place 0's in the output
, - comma seperator
. - used in decimal point numbers
For Ex,
Let us say we want to convert number 44444.44 to character string like '$44,444.44' then we
can use following,
SELECT To_Char (44444.44, '$99,999.99') From Dual;
This will give output as $44,444,44. So, this is how we can convert Numbers into Character Data
using format models.
For Date conversions format models can be used are,
MM - 2 digit month (Ex: 20)
MON - 3 character month (Ex: 'JUN')
Month - Full name of Month (Ex: 'JUNE')
DD - 2 digit date (Ex: 11)
DY - 3 character day (Ex : 'MON')
DAY - Full name of Day (Ex: 'MONDAY')
YY - 2 digit year (Ex: 99)
YYYY - 4 digit year (Ex: 1999)
Year - year spelled out (Ex: Nineteen Ninty Nine)
MI - Minutes
SS - Seconds
HH12 - Hours in 12hr format
HH24 - Hours in 24hr format
fm - can be used to suppress leading 0's in day of input date
(Ex: 09 will be shown as 9)
sp - used to spell out the day in input date (Ex: seven)
th - used to add th or nd or rd in output while spelling out day in input date
-, . , / - separators used to separate day, month and year in output
Ex,
Select To_char ('14-jul-2012', 'fmddspth "of" Month YYYY') From dual;
This will give output as 14th of July 2012. we can use "of" also in output string as shown.
While using this function we must keep in mind that the format string which we will provide to the function will be that of in which format we want the output to be displayed.
So, this is how we can convert Numeric or Date data into character data using To_Char function.
To_Number and To_Date Functions ::
To_Number function is used to convert character data to Numeric data. To_Date function is used to convert character data to date data. In order to convert Character data to Numeric or Date data input character data must contain valid Numbers or dates respectively.
Syntax :: To_Number (i/p char, format)
To_Date (i/p char, format)
In both syntax i/p char refers to the input character data which we want to convert to numeric data or Date data.
format refers to the format in which we are providing input character data .
Here also we use format models. These are same as which we have discussed in To_Char function.
Ex : SELECT To_Number ('$46,567.67', '$99,999.99') From Dual;
This will give output as 46567.67.
SELECT To_Date ('19/04/1999','dd/mm/yyyy') From Dual;
This will give output as '19-Apr-1999' (Remember this is default and valid format of dates in SQL).
This is how we can convert Character data to Numeric or Date data.
Note : The most important point to keep in mind while working with To_Char, To_Number and To_Date functions is,
--> In To_Char function the format we will provide is of output.
--> In To_Date and To_Number functions format we will provide is of input.
