TO_CHAR Function with SQL

TO_CHAR Function
The TO_CHAR function is used to convert numbers or dates into strings. It allows you to format the output according to your needs using various format models.
Syntax 

TO_CHAR(expression [, format])

expression: The value you want to convert to a string.
format (optional): The format model for conversion.

Examples
Converting a Date to a String 

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS formatted_date
FROM dual;

Result: The current date is displayed in the format 25-AUG-2024.

Converting a Number to a String with Formatting 

SELECT TO_CHAR(1234.56, '9999.99') AS formatted_number
FROM dual;

Result: The number 1234.56 is displayed as 1234.56.

Converting a Number with Thousand Separators 

SELECT TO_CHAR(1234567.89, '9,999,999.99') AS formatted_number
FROM dual;

Result: The number 1234567.89 is displayed as 1,234,567.89.

TO_NUMBER Function
The TO_NUMBER function converts a string into a number. You can specify a format model to handle different numeric formats.
Syntax

TO_NUMBER(expression [, format])

 expression: The string you want to convert to a number.
format (optional): The format model for conversion.

Examples
Converting a String to a Number 

SELECT TO_NUMBER('1234.56') AS numeric_value
FROM dual;

Result: The string ‘1234.56’ is converted to the number 1234.56.

Converting a String with a Format Model 

SELECT TO_NUMBER('1,234.56', '9,999.99') AS numeric_value
FROM dual;

Result: The string ‘1,234.56’ is converted to the number 1234.56, using the format model with thousand separators.
Handling Non-Numeric Characters 

SELECT TO_NUMBER('1234.56abc') AS numeric_value
FROM dual;

Result: This will return an error because ‘1234.56abc’ cannot be converted to a number.

TO_DATE Function
The TO_DATE function is used to convert a string into a DATE value. You can specify a format model to match the string’s format.
Syntax 

TO_DATE(expression [, format])

expression: The string you want to convert to a date.
format (optional): The format model for the conversion.

Examples
Converting a String to a Dat

SELECT TO_DATE('25-08-2024', 'DD-MM-YYYY') AS date_value
FROM dual;

 Result: The string ’25-08-2024′ is converted to a DATE value.
Converting a String with Different Date Formats 

SELECT TO_DATE('August 25, 2024', 'Month DD, YYYY') AS date_value
FROM dual;

Result: The string ‘August 25, 2024’ is converted to a DATE value using the format Month DD, YYYY.
Including Time in Date Conversion 

SELECT TO_DATE('25-08-2024 15:30:00', 'DD-MM-YYYY HH24:MI:SS') AS date_time_value
FROM dual;

Result: The string ’25-08-2024 15:30:00′ is converted to a DATE value including time.
Comparison and Combined Usage
These functions can be used together in queries to format and manipulate data flexibly.
Example: Formatting a date for display and converting a string to a number before performing a calculation 

SELECT
TO_CHAR(TO_DATE('25-08-2024', 'DD-MM-YYYY'), 'DD/MM/YYYY') AS formatted_date,
TO_NUMBER('1234.56') + 100 AS total_amount
FROM dual;

 
Result: The date is displayed as 25/08/2024, and the sum 1234.56 + 100 is calculated to give 1334.56.

Conclusion
The TO_CHAR, TO_NUMBER, and TO_DATE functions are powerful tools in Oracle SQL for converting between different data types and formatting data as needed. Understanding how to use these functions effectively allows you to handle and display data in various formats and perform necessary data transformations and calculations.