How To Use TO_CHAR In Oracle

How To Use TO_CHAR In Oracle

December 12, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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.

TO_CHAR(<value_to_convert_to_text>[,<a_data_mask>]);

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;

dba oracle dba oracle database management system database oracle performance tuning oracle to_char oracle sql to_char in oracle sql to_char linux server linux

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;

dba oracle dba oracle database management system database oracle performance tuning oracle to_char oracle sql to_char in oracle sql to_char linux server linux

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;

dba oracle dba oracle database management system database oracle performance tuning oracle to_char oracle sql to_char in oracle sql to_char linux server linux oracle mask elements for numbers

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;

dba oracle dba oracle database management system database oracle performance tuning oracle to_char oracle sql to_char in oracle sql to_char linux server linux oracle mask numbers

The list of possible oracle data format masks are on this oracle link.

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*