Conversion function in oracle
Conversion Functions are divided as below:
1. TO_NUMBER:- This function converts character to number i.e. char or varchar2 datatype to number datatype. Here it is important to note that string value must contains 0-9,decimal-point and + or – sign.
Syntax
TO_NUMBER(str)
In above syntax “str” is any string value that contains 0-9,decimal-point and + or – sign.
Use of TO_NUMBER function
2. TO_CHAR:- This function converts number to character i.e. number datatype to char or varchar2 datatype. We have to use particular format which consist of “0”, “9” and “,”.
Syntax
TO_CHAR(n,format)
In above syntax “n” is any numeric value and “format” consist of “0”, “9”and “,”.
Use of TO_CHAR function
NOTE:- Here in above query it is important to note that numeric value passed as first argument(n) is formatted from right side according to second argument(format) pattern.
Use of TO_CHAR function
NOTE:- :- Here second argument in function we have placed “0” at first position hence “0” will be left padded in output to display it exactly as per format.
3. TO_CHAR:- This function converts date to character i.e. date datatype to char or varchar2 datatype.
Syntax
TO_CHAR(date,format)
In above syntax “date” is valid date and “format” is any valid date format combined with the abbreviation mention in below table. If format is omitted then default format is “DD-MON-YY”.
| Abbreviation | Specifies |
| MM | Numeric month (e.g., 07) |
| MON | Abbreviated month name (e.g., JUL) |
| MONTH | Full month name (e.g., JULY) |
| DD | Day of month (e.g., 24) |
| DY | Abbreviated name of day (e.g., FRI) |
| YYYY | 4-digit year (e.g., 1998) |
| YY | Last 2 digits of the year (e.g., 98) |
| WW | No. of week in month |
| HH | Hour of day (1-12) |
| MI | Minute (0-59) |
| SS | Second (0-59) |
Use of TO_CHAR function
Use of TO_CHAR function
4. TO_DATE:- This function converts character to date i.e. char or varchar2 datatype to date datatype.
Syntax
TO_DATE(str,format)
In above syntax “str” is date in any format and “format” is any valid date format. If “str” is in “DD-MON-YY” format then we can omit second argument “format”.
Use of To_DATE function
Use of To_DATE function
Use of To_DATE function
I hope this article will help you