This online tutorial is based on examples to make it easier to follow. Function Oracle TO_CHAR converts values like NCHAR, NVARCHAR2, CLOB, NCLOB, or NUMBER to character type and the To_Char function always returns VARCHAR2 type.
The difference can be seen in the following two SQL queries. The first Select statement is without TO_CHAR function and the second is with. Both queries are using the same value.
SELECT 01234 FROM DUAL;
The first SQL query without Oracle To_Char returned the number 01234 without the zero in front. This happens because the Number type will be trimmed by Oracle database in the way it would be less confusing for users. Number 01234 became now 1234 (one thousand two hundred thirty three). To keep the number with the original look, you should use Oracle To_Char function and store it into the Oracle database table as VARCHAR2 type. It is important to know to for what the number belongs because the phone numbers or credit card numbers starting with 0 (zero) would NOT be valid after the first zero is removed.
The second Oracle DBA example shows how to convert the same number value to text without loosing any number in front.
SELECT TO_CHAR('01234') FROM DUAL;
The SQL query output returned a number as 01234 since we declared it as a string.
Also the look of a number can be modified using character data mask for numbers. This is the direct link to possible mask elements. The data mask should be used inside the Oracle To_Char function as the following example demonstrates. The mask begins with 99 and that does mean an optional number. The 0-s meaning obligated output of numbers and when they are missing the mask replace them with zeros. The D-letter defines a coma place.
SELECT TO_CHAR(111.2,'990D00') AS formatted_number FROM DUAL;
The output shows “111.20” and this has been formatted from number “111.2“. Using To_Char with a mask is commonly used on reports or web sites to give a number more common look.
Also the Oracle To_Char can be used with Oracle date type and to format the date in a more preferable look. When we are talking about the data mask in Oracle To_Char then different data types may have different keywords to declare the values. For example the following Oracle To_Char date mask is declared as year-month-day as (YYYY-MM-DD) and hours in 24 digits ,minutes and seconds as (HH24:MI:SS). Take a look at the output to see the date matches with the format.
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS date_to_char FROM DUAL;