PL/SQL TO_CHAR

Introduction to PL/SQL TO_CHAR

PL/SQL TO_CHAR is an inbuilt function which is used to convert the datetime, interval, and numerical values in the string format. In addition, it converts the various data types like DATE, TIMESTAMP, etc., into the varchar data type. It is one of the important functions used widely by the users working on the database to perform conversions on data types according to the program requirements. TO_CHAR function takes 3 parameters in which one parameter, which is the expression that news to be converted, is mandatory, and the other two parameters, which are format and nls_language, are optional.

Syntax of PL/SQL TO_CHAR

Given below is the basic syntax of using the TO_CHAR function in PL/SQL:

TO_CHAR (expression [ , date_format] [ , nls_language] )

Where,

• expression: It is a date, number, or timestamp that must be converted into a string.
• date_format: date_format specifies the string in which the user wants the resulting value to be in. It is an optional argument.
• nls_language: nls_language specifies the language in which the name and the abbreviations of month and day should be present in the result. It is an optional argument.

Return Type:

• TO_CHAR function returns the string value by converting the date or interval value in the format specified by the user.

How does TO_CHAR Function Work in PL/SQL?

Some of the important points describing how the TO_CHAR function work in PL/SQL is given below:

• TO_CHAR function in PL/SQL is used to convert the datetime or interval value, i.e., DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data type value in the data type value of varchar2.
• date_format and nls_language are the optional arguments in the TO_CHAR function.
• If the ‘date_format’ argument is not specified in the TO_CHAR function, oracle uses the default date format for DATE values, timestamp format for TIMESTAMP values, and default timestamp with timezone format for TIMESTAMP WITH TIME ZONE values and default timestamp format for TIMESTAMP and TIMESTAMP WITH TIME ZONE values. Numeric values will be converted to quite long string values, which will not hold by the significant digits.
• If the ‘nls_language’ argument is not specified in the TO_CHAR function, oracle uses the default date language.
• In order to use the ‘nls_language’ argument, the user can use any of the languages like German, French, Swedish, etc., mentioned by the Oracle in the official document.
• Argument ‘expression’ is a mandatory argument, and it specifies the expression, which can be datetime, interval, or any number values that need to be converted in the string according to the format given in the function.
• Argument ‘date_format’ specifies the format in which the resulting string should be displayed to the user.
• Argument nls_langauge specifies the names and abbreviations that should be used for the month and day values. For e.g., Tuesday, Tues, February, Feb, etc.
• Argument ‘nls_language’ is of the following form mentioned below:

‘NLS_DATE_LANGUAGE = language’

• Function TO_CHAR in Oracle is similar to the CAST or CONVERT function used in SQL Server.
• The input values in the TO_CHAR function can be BINARY_FLOAT, BINARY_DOUBLE, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE.

Oracle versions which support the TO_CHAR function are mentioned below:

• Oracle 12c
• Oracle 11g
• Oracle 10g
• Oracle 9i
• Oracle 8i

Examples of PL/SQL TO_CHAR

Given below examples shows how the TO_CHAR function is used in the PL/SQL code practically:

Example #1

Code:

SELECT TO_CHAR(67893.67, '99999.9') FROM DUAL;

Output:

Explanation:

• In the above code, the expression parameter and the format parameter are passed in the TO_CHAR function. Since in the format given by the user, there is only one value after the decimal point.
• So, the given expression is converted in the string according to the passed format. In the resulting string, there is only one value after the decimal point. So the expression value ‘67893.67’ gets rounded off, and only one value is displayed after the decimal point.

Example #2

Code:

SELECT TO_CHAR(1245, '000000') FROM DUAL;

Output:

Explanation:

• In the above code, expression and the format is passed in the TO_CHAR function, which is 1245 and 000000, respectively. So the given expression is converted according to the passed format. Since the format passed is of 6 digits, the resulting value would be a string with the 6 digits, and to do this, 0’s will get appended to the left of the expression value.
• The result 001245 would be displayed to the user on the console.

Example #3

Code:

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;

Output:

Explanation:

• In the above code, the expression which is SYSDATE and the format ‘YYYY/MM/DD’ is passed in the TO_CHAR function. Thus, SYSDATE will display the current system date in a particular format.
• But with the passing of the format parameter, the system date would now be displayed in the specified format. So the resulting value is 2021/06/16.

Example #4

Code:

SELECT TO_CHAR(SYSDATE, ‘Mon’) FROM DUAL;

Output:

Explanation:

• In the above code, the expression SYSDATE and the format ‘Mon’ (basically an abbreviation of Month) are passed in the TO_CHAR function.
• Since the SYSDATE would display the system’s current date in a particular format. But now, in the above code, as the format is mentioned, only the month in the abbreviated form will be displayed to the user. So, according to that criteria, Jun is displayed as output.

Example #5

Code:

SELECT TO_CHAR(SYSDATE) FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'Mon DD, YYYY';
SELECT TO_CHAR(SYSDATE) FROM dual;

Output:

Explanation:

• In the above code, SYSDATE is retrieved using the TO_CHAR function and displayed on the screen. Now the NLS_DATE_FORMAT is changed according to the one given by the user, which is the Month abbreviation Date year in 4 digits.
• Now the session is set according to the format given by the user. Now on calling the SYSDATE using the TO_CHAR function will display the date in the format already set above. So, the system date is displayed as ‘Jun 16, 2021’ to the user.

Conclusion

The above description clearly explains what the TO_CHAR function is in PL/SQL and how it works. However, date and Time are frequently required in the programs and databases for various purposes, so its conversion in various forms and datatypes needs to be very clear. So it is important for a programmer to understand these conversions clearly in order to work accordingly in the code.

Recommended Articles

This is a guide to PL/SQL TO_CHAR. Here we discuss the introduction, how does TO_CHAR function work in PL/SQL? and examples. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL