Adsense Ad

Wednesday, 3 May 2017

Oracle SQL / PLSQL: TO_CHAR Function


Definition:
The Oracle TO_CHAR function converts a string, date, a datetime-interval or a number into a character string.

If the first parameter is DATE type, it will be converted to a string representation. The resulting string style is specified with DATETYPE format elements which are optional parameters in the second parameter. 

Example Syntax:

TO_CHAR(nchar_clob_or_nclob)
TO_CHAR (datetime_or_interval)
TO_CHAR (datetime_or_interval, 'format_string')
TO_CHAR (datetime_or_interval, 'format_string', 'nlsparam')

TO_CHAR (number)
TO_CHAR (number,'format_string')
TO_CHAR (number,'format_string', 'nlsparam')





Parameters or Arguments

value
A number or date that will be converted to a string.
format_mask
Optional. This is the format that will be used to convert value to a string.
nls_language
Optional. This is the nls language used to convert value to a string.

Applies To

The TO_CHAR function can be used in the following versions of Oracle/PLSQL:

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

Example

Let's look at some Oracle TO_CHAR function examples and explore how to use the TO_CHAR function in Oracle/PLSQL.

Examples with Numbers

For example:
The following are number examples for the TO_CHAR function.
TO_CHAR(1210.73, '9999.9') 
Result: ' 1210.7' 

TO_CHAR(-1210.73, '9999.9') 
Result: '-1210.7' 

TO_CHAR(1210.73, '9,999.99') 
Result: ' 1,210.73' 

TO_CHAR(1210.73, '$9,999.00') 
Result: ' $1,210.73' 

TO_CHAR(21, '000099') 
Result: ' 000021'

Examples with Dates

The following is a list of valid parameters when the TO_CHAR function is used to convert a date to a string. These parameters can be used in many combinations.

ParameterExplanation
YEARYear, spelled out
YYYY4-digit year
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY4-digit year based on the ISO standard
QQuarter of year (1, 2, 3, 4; JAN-MAR = 1).
MMMonth (01-12; JAN = 01).
MONAbbreviated name of month.
MONTHName of month, padded with blanks to length of 9 characters.
RMRoman numeral month (I-XII; JAN = I).
WWWeek of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
WWeek of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IWWeek of year (1-52 or 1-53) based on the ISO standard.
DDay of week (1-7).
DAYName of day.
DDDay of month (1-31).
DDDDay of year (1-366).
DYAbbreviated name of day.
JJulian day; the number of days since January 1, 4712 BC.
HHHour of day (1-12).
HH12Hour of day (1-12).
HH24Hour of day (0-23).
MIMinute (0-59).
SSSecond (0-59).
SSSSSSeconds past midnight (0-86399).
FFFractional seconds.
The following are date examples for the TO_CHAR function.
TO_CHAR(sysdate, 'yyyy/mm/dd') 
Result: '2003/07/09' 

TO_CHAR(sysdate, 'Month DD, YYYY') 
Result: 'July 09, 2003' 

TO_CHAR(sysdate, 'FMMonth DD, YYYY') 
Result: 'July 9, 2003' 

TO_CHAR(sysdate, 'MON DDth, YYYY') 
Result: 'JUL 09TH, 2003' 

TO_CHAR(sysdate, 'FMMON DDth, YYYY') 
Result: 'JUL 9TH, 2003' 

TO_CHAR(sysdate, 'FMMon ddth, YYYY') 
Result: 'Jul 9th, 2003'

You will notice that in some TO_CHAR function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

TO_CHAR(sysdate, 'FMMonth DD, YYYY') Result: 'July 9, 2003' TO_CHAR(sysdate, 'FMMON DDth, YYYY') Result: 'JUL 9TH, 2003' TO_CHAR(sysdate, 'FMMon ddth, YYYY') Result: 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions

Question: Why doesn't this sort the days of the week in order?
SELECT ename, hiredate, TO_CHAR((hiredate),'fmDay') "Day" FROM emp ORDER BY "Day";

Answer:

In the above SQL, the fmDay format mask used in the TO_CHAR function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:
SELECT ename, hiredate, TO_CHAR((hiredate),'fmD') "Day" FROM emp ORDER BY "Day";




TO_CHAR Complete World:
Convert Datatype To StringTO_CHAR(<string_or_column>, <format>) RETURN VARCHAR2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS') FROM DUAL;

Converts  DATE and  TIMESTAMP to VARCHAR2  with the specified format
The "X" in the ROUND andTRUNC column indicates that these symbols with these functions
TO_DATE(<date_string>, <format mask>, <NLS_PARAMETER>)
-- before running these demos
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
 
SymbolDescriptionROUNDTRUNC
CCOne greater than the first two digits of a four-digit yearXX
SQL> SELECT TO_CHAR(SYSDATE, 'CC') FROM DUAL;

TO_CHAR(SYSDATE,'CC')
---------------------------------------------
21
DStarting day of the weekXX
SQL> SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;

TO_CHAR(SYSDATE,'D')
--------------------------------------------
4
DDDayXX
SQL> SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;

TO_CHAR(SYSDATE,'DD')
---------------------------------------------
02
DDDDayXX
SQL> SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

TO_CHAR(SYSDATE,'DDD')
----------------------------------------------
093
DAYStarting day of the week XX
SQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

TO_CHAR(SYSDATE,'DAY')
----------------------------------------------
WEDNESDAY
DYStarting day of the week XX
SQL> SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;

TO_CHAR(SYSDATE,'DY')
----------------------------------------------
WED
HHHours XX
SQL> SELECT TO_CHAR(SYSDATE, 'HH') FROM DUAL;

TO_CHAR(SYSDATE,'HH')
---------------------------------------------
10
HH12Hours  
SQL> SELECT TO_CHAR(SYSDATE, 'HH12') FROM DUAL;

TO_CHAR(SYSDATE,'HH12')
-----------------------------------------------
10
HH24Hours  
SQL> SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL;

TO_CHAR(SYSDATE,'HH24')
-----------------------------------------------
22
IISO YearXX
SQL> SELECT TO_CHAR(SYSDATE, 'I') FROM DUAL;

TO_CHAR(SYSDATE,'I')
--------------------------------------------
8
IWSame day of the week as the first day of the ISO year  
SQL> SELECT TO_CHAR(SYSDATE, 'IW') FROM DUAL;

TO_CHAR(SYSDATE,'IW')
---------------------------------------------
14
IYISO Year  
SQL> SELECT TO_CHAR(SYSDATE, 'IY') FROM DUAL;

TO_CHAR(SYSDATE,'IY')
---------------------------------------------
08
IYYISO Year  
SQL> SELECT TO_CHAR(SYSDATE, 'IYY') FROM DUAL;

TO_CHAR(SYSDATE,'IYY')
------------------------------------------------
008
IYYYISO Year  
SQL> SELECT TO_CHAR(SYSDATE, 'IYYY') FROM DUAL;

TO_CHAR(SYSDATE,'IYYY')
------------------------------------------------
2008
JJulian Day  
SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

TO_CHAR(SYSDATE,'J')
--------------------------------------------
2454559
MIMinutes XX
SQL> SELECT TO_CHAR(SYSDATE, 'MI') FROM DUAL;

TO_CHAR(SYSDATE,'MI')
---------------------------------------------
29
MMMonth (rounds up on the sixteenth day)  
SQL> SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL;

TO_CHAR(SYSDATE,'MM')
---------------------------------------------
04
MONMonth (rounds up on the sixteenth day) XX
SQL> SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL;

TO_CHAR(SYSDATE,'MON')
----------------------------------------------
APR
MONTHMonth (rounds up on the sixteenth day) XX
SQL> SELECT TO_CHAR(SYSDATE, 'MONTH') FROMDUAL;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------------------
APRIL
QQuarter (rounds up on 16th day of the 2nd month of the quarter)  
SQL> SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;

TO_CHAR(SYSDATE,'Q')
--------------------------------------------
2
RMMonth (rounds up on the sixteenth day) in Roman Numerals  
SQL> SELECT TO_CHAR(SYSDATE, 'RM') FROM DUAL;

TO_CHAR(SYSDATE,'RM')
---------------------------------------------
IV
SCCOne greater than the first two digits of a four-digit yearXX
SQL> SELECT TO_CHAR(SYSDATE, 'SCC') FROM DUAL;

TO_CHAR(SYSDATE,'SCC')
----------------------------------------------
21
SYYYYYear (rounds up on July 1)XX
SQL> SELECT TO_CHAR(SYSDATE, 'SYYYY') FROMDUAL;

TO_CHAR(SYSDATE,'SYYYY')
------------------------------------------------
2008
WWeek number in the month  
SQL> SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL;

TO_CHAR(SYSDATE,'W')
--------------------------------------------
1
WWWeek of the year  
SQL> SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;

TO_CHAR(SYSDATE,'WW')
---------------------------------------------
14
YOne Digit YearXX
SQL> SELECT TO_CHAR(SYSDATE, 'Y') FROM DUAL;

TO_CHAR(SYSDATE,'Y')
--------------------------------------------
8
YYTwo Digit YearXX
SQL> SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL;

TO_CHAR(SYSDATE,'YY')
---------------------------------------------
08
YYYThree Digit YearXX
SQL> SELECT TO_CHAR(SYSDATE, 'YYY') FROM DUAL;

TO_CHAR(SYSDATE,'YYY')
----------------------------------------------
008
YYYYFour Digit YearXX
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;

TO_CHAR(SYSDATE,'YYYY')
-----------------------------------------------
2008

CREATE TABLE t (
datecol1 DATE,
datecol2 DATE);

INSERT INTO t (datecol1, datecol2) VALUES (SYSDATESYSDATE);

col col1 format a30
col col2 format a20
col col3 format a20
col "Financial Quarter" format a20

SELECT TO_CHAR(datecol1, 'DAY-MONTH-YY') COL1,
       TO_CHAR(datecol2, 'D-MM/YYYY HH24:MI:SS') COL2,
       TO_CHAR(datecol2, 'YYYY-MON-DD HH24') COL3,
       TO_CHAR(datecol2, 'Q') "Financial Quarter"
FROM t;
Demo of FM Formatting From Tom Kyte (Oracle Magazine 3-4/2004)SELECT TO_CHAR(dt, 'HH:MI AM') A,
       TO_CHAR(dt, 'FMHH:MI AM') B,
       TO_CHAR(dt, 'FMHHFM:MI AM') C
FROM (SELECT TO_DATE('09:01 am', 'HH:MI AM') dt FROM DUAL);
Convert NUMBER to CHARACTERTO_CHAR(number)
SELECT TO_CHAR(123)
FROM DUAL;
Convert NUMBER to HEXTO_CHAR(NUMBERRETURN HEX
SELECT TO_CHAR(1048576,'XXXXXXXX')
FROM DUAL;
 Click To View Referenced Site

No comments: