to_char and to_nchar [Oracle SQL]

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')

TO_CHAR is an SQL function that converts a string, date, a datetime-interval or a number into a character string.

Converting a date

If the first parameter is date type, it can be converted to a string representation. The appearance of that representation can be controlled with a Datetype format elements which are (optionally) in the second parameter.

select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual;

Format strings

Converting numbers

Suppressing leading or trailing blanks

If a number is formatted with 0000, a blank will be added on the left side if the number is positive. This is to make room for the minus symbol for numbers that are negative.
In order to suppress this blank, the format FM can be used.

Converting a number into a hex string

select to_char('419','xxx') from dual;

Converting dates

Abbriviated name of the day

The abbrivation of a day's name (such as SUN, MON ... can be retrieved with the format specifier DY. As to_char is sensitive to the actual nls setting, I use nls_date_language=english to make sure that the english abbreviation is returned, regardless of the nls setting.

select to_char(sysdate,'DY','nls_date_language=english') from dual

Spelling a number

SQL> select to_char(to_date('429','J'),'Jsp') from dual;

TO_CHAR(TO_DATE('429','J
------------------------
Four Hundred Twenty-Nine

Weekday (day of week)

to_char(some_date,'D') returns a number indicating the weekday. However, for a given date, this number returned varies with NLS_TERRITORY:

SQL> alter session set nls_territory='UNITED KINGDOM';

Session altered.
SQL> select to_char(sysdate,'D') from dual

T
-
4

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> select to_char(sysdate,'D') from dual;

T
-
5

I executed these select statements on a thursday. So, in America, Thursday seems to be the 5^th day in the week while it is the 4^th in the UK.

nls parameters

select to_char(444945.200, '99999999d00', 'nls_numeric_characters='', ''') from dual;

 444945,20


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback