|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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')
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.
Possible format strings when the first argument to to_char is a date:
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;
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
Weekday (day of week)
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 5th day in the week while it is the 4th in the UK.
The behaviour of to_char and to_nchar can be influenced by setting the NLS_DATE_LANGUAGE, NLS_CALENDAR, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY and NLS_ISO_CURRENCY NLS parameters.
select to_char(444945.200, '99999999d00', 'nls_numeric_characters='', ''') from dual;
Thanks to Olivier Holl who notified me of a type on this page.