TO_CHAR

The TO_CHAR function in Oracle is used to convert a value of a certain datatype into a string representation. This is particularly useful for formatting numbers, dates, and timestamps in a desired format. The function allows for extensive customization and formatting options.

Here's a detailed breakdown of TO_CHAR:

Syntax

TO_CHAR(expression, format_mask, 'nls_param')

• expression: This is the value or column you want to convert. It can be a number, date, or timestamp.
• format_mask: This optional parameter allows you to define how the output should be formatted. The available masks depend on the data type of the expression.
• nls_param: This is an optional parameter that can be used to specify the NLS (National Language Support) parameters, such as language or territory.

 

1. Using TO_CHAR for Dates and Timestamps

You can use TO_CHAR to convert DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE values to string representations with specific formats.

Date Format Examples

Oracle provides a wide range of formatting options for dates. Some common format models are:

• YYYY: Year in 4 digits (e.g., 2025)
• MM: Month (01 to 12)
• DD: Day of the month (01 to 31)
• HH: Hour of day (01 to 12)
• HH24: Hour of day (00 to 23)
• MI: Minute (00 to 59)
• SS: Second (00 to 59)
• AM: AM/PM notation

Example:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

Output:

2025-02-24 15:30:00

Common Date Format Models:

• YYYY-MM-DD: Year-Month-Day format.
• DD-Mon-YYYY: Day-Month-Year format with abbreviated month name (e.g., 24-Feb-2025).
• HH:MI:SS AM: 12-hour format with AM/PM notation.
• FMDDth Mon YYYY: Day of the month with an ordinal suffix (e.g., 24th Feb 2025).

Format Modifiers:

• FM: Removes leading and trailing spaces.
• AD: Displays the year as "Anno Domini."
• TH: Adds ordinal suffix (e.g., 1st, 2nd, etc.).

Example with modifiers:

SELECT TO_CHAR(SYSDATE, 'FMDDth Month YYYY') FROM dual;

Output:

24th February 2025

 

2. Using TO_CHAR for Numbers

The TO_CHAR function can also format numbers as strings, allowing you to specify the number of digits, decimal points, and grouping.

Number Format Examples

• 999: Up to three digits.
• 9999.99: Four digits with two decimal places.
• 9,999: Adds thousands separator.
• 999.99: Allows two decimal points.
• 0: Used to show leading zeroes.
• 9: Used for optional digit placeholders.

Example:

SELECT TO_CHAR(12345.6789, '99999.99') FROM dual;

Output:

12345.68

Common Number Format Models:

• 9,999: Adds a thousands separator (e.g., 1,000).
• 999.99: Standard number with 2 decimal places.
• 00.00: Forces 2 decimal places even for whole numbers (e.g., 5.00).
• 9.99: Use of optional digits, e.g., if the number is 5.3, it becomes 5.30.

Example with currency:

SELECT TO_CHAR(12345.6789, '99999.99') FROM dual;

Output:

12345.68

 

3. Using TO_CHAR with NLS Parameters

The nls_param is used to apply language and territory specific formatting rules. For example, when formatting dates, you might want to change the language of the month names.

Example (Using NLS Parameters):

SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY', 'NLS_DATE_LANGUAGE=SPANISH') FROM dual;

Output:

febrero 24, 2025

You can use various NLS parameters like:

• NLS_DATE_LANGUAGE: Sets the language for date-related output.
• NLS_NUMERIC_CHARACTERS: Defines the decimal and grouping characters.
• NLS_CURRENCY: Used for formatting currency.

 

4. TO_CHAR with Conditional Logic

You can use TO_CHAR with other SQL functions like CASE to apply conditional logic when formatting values.

Example:

SELECT TO_CHAR(SYSDATE, 'Day, MM-DD-YYYY') FROM dual;

Output:

Saturday, 02-24-2025

5. Performance Considerations

• Date Conversion: Converting dates into strings with a specific format could be computationally expensive if done in large datasets.
• Index Usage: Using TO_CHAR on a column may prevent the use of indexes, so be careful if performance is crucial for queries with a large amount of data.

 

Key Takeaways

• The TO_CHAR function is versatile and can be used for converting dates, timestamps, and numbers to strings.
• Formatting strings can be tailored using format masks for dates and numbers.
• The nls_param parameter allows localization of formats.
• The function is essential for formatting output and can handle a wide range of scenarios (from simple date formatting to more complex number formatting).

Would you like more examples or explanations of any particular aspect of TO_CHAR?