TO_CHAR Oracle Function

With the function TO_CHAR, Oracle converts a number or date to a string.

The to_char Oracle syntax is:

to_char( value, [ format_mask ], [ nls_language ] )

• value can either be a number or date that will be converted to a string.
• format_mask is optional. This is the format that will be used to convert value to a string.
• nls_language is optional. This is the nls language used to convert value to a string.

So, to convert a date variable to a text (string), you would use the to_char Oracle function.

TO_CHAR Oracle within SQL 🔗

select to_char(sysdate, ‘yyyy/mm/dd’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘2011/07/17’ */

select to_char(sysdate, ‘Month DD, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘October 17, 2011’ */

select to_char(sysdate, ‘FMMonth DD, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘October 17, 2011’ */

select to_char(sysdate, ‘FMMON DDth, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘OCT 17TH, 2011’ */

select to_char(sysdate, ‘FMMon ddth, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘Oct 17th, 2011’ */

TO_CHAR Oracle within PL/SQL 🔗

(Please turn serveroutput on)

SET serveroutput ON

Here, within PL/SQL loop with the function to_char oracle returns the number i converted to a text string:

BEGIN

FOR i IN REVERSE 1 .. 3 LOOP

DBMS_OUTPUT.put_line(TO_CHAR(i) ||’ - converted by to_char Oracle function’);

END LOOP;

END;

Result:

3 - converted by to_char Oracle function

2 - converted by to_char Oracle function

1 - converted by to_char Oracle function

Update on TO CHAR Oracle Function 🔗

The information provided about the TO CHAR Oracle function in the year 2011 is still valid for the years 2021 to 2024. This function is used to convert a number or date to a string in Oracle. The syntax for TO CHAR Oracle is as follows:

TO_CHAR(value, format_mask, nls_language)

The value parameter can be either a number or a date that will be converted to a string. The format_mask parameter is optional and specifies the format to be used for the conversion. The nls_language parameter is also optional and defines the language used for the conversion.

To convert a date variable to a text string using TO CHAR Oracle in SQL, you can use the following syntax:

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD') AS TO_CHAR_ORACLE
FROM dual;

In the year 2024, the above SQL query will return the current date in the format YYYY-MM-DD. The TO CHAR Oracle function can also be used in PL/SQL, as shown below:

SET serveroutput ON;
BEGIN
    FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(i) || ' converted by TO CHAR Oracle function');
    END LOOP;
END;

In the year 2024, the PL/SQL code snippet above will output the numbers from 10 to 1 converted to text strings using the TO CHAR Oracle function.

Overall, the TO CHAR Oracle function remains a useful tool for converting numbers or dates to strings in Oracle SQL and PL/SQL, and the information provided in 2011 is still applicable in the years 2021 to 2024.