Specifying NLS Parameters in SQL Functions

NLS parameters are specified in SQL functions as 'parameter = value'. For example:

'NLS_DATE_LANGUAGE = AMERICAN'

The following NLS parameters can be specified in SQL functions:

NLS_DATE_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY
NLS_CALENDAR
NLS_SORT

Table: SQL Functions and Their Valid NLS Parameters shows which NLS parameters are valid for specific SQL functions.

SQL Functions and Their Valid NLS Parameters

SQL Function	Valid NLS Parameters
`TO_DATE`	`NLS_DATE_LANGUAGE, NLS_CALENDAR`
`TO_NUMBER`	`NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY`,
`TO_CHAR`	`NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR`
`TO_NCHAR`	`NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR`
`NLS_UPPER`	`NLS_SORT`
`NLS_LOWER`	`NLS_SORT`
`NLS_INITCAP`	`NLS_SORT`
`NLSSORT`	`NLS_SORT`

Example: Using NLS Parameters in SQL Functions shows how to use NLS parameters in SQL functions.

Using NLS Parameters in SQL Functions

SELECT TO_DATE('1-JAN-99', 'DD-MON-YY', 
   'NLS_DATE_LANGUAGE = American') "01/01/99" FROM DUAL;

SELECT TO_CHAR(hire_date, 'DD/MON/YYYY',
   'NLS_DATE_LANGUAGE = French') "Hire Date" FROM employees;

SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY', 
   'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ') "System Date" FROM DUAL;

SELECT TO_CHAR(13000, '99G999D99', 
  'NLS_NUMERIC_CHARACTERS = '',.''') "13K" FROM DUAL;

SELECT TO_CHAR(salary, '99G999D99L', 'NLS_NUMERIC_CHARACTERS = '',.''
   NLS_CURRENCY = ''EUR''') salary FROM employees;

SELECT TO_CHAR(salary, '99G999D99C', 'NLS_NUMERIC_CHARACTERS = ''.,''
   NLS_ISO_CURRENCY = Japan') salary FROM employees;

SELECT NLS_UPPER(last_name, 'NLS_SORT = Swiss') "Last Name" FROM employees;

SELECT last_name FROM employees 
   ORDER BY NLSSORT(last_name, 'NLS_SORT = German');

Note:

In some languages, some lowercase characters correspond to more than one uppercase character or some uppercase characters correspond to more than one lowercase characters. As a result, the length of the output from the NLS_UPPER, NLS_LOWER, and NLS_INITCAP functions can differ from the length of the input.