Oracle7 Server Reference Manual

Contents Index Home Previous Next

Using NLS Parameters in SQL Functions

All character functions support both single-byte and multi-byte characters. Except where explicitly stated, character functions operate character-by-character, rather than byte-by-byte.

All SQL functions whose behavior depends on NLS conventions allow NLS parameters to be specified. These functions are

Explicitly specifying the optional NLS parameters for these functions allows the function evaluations to be independent of the NLS parameters in force for the session. This feature may be important for SQL statements that contain numbers and dates as string literals.

For example, the following query is only evaluated correctly if the language being for dates is American:

SELECT ENAME FROM EMP
WHERE HIREDATE > '1-JAN-91'

Such a query can be made independent of the current date language using:

SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE('1-JAN-91','DD-MON-YY',
   'NLS_DATE_LANGUAGE = AMERICAN')

In this way, language-independent SQL statements can be defined where necessary. For example, such statements may be necessary when string literals appear in SQL statements in views, CHECK constraints, or triggers.

Default Specifications

When evaluating views and triggers, default values for NLS function parameters are taken from the values currently in force for the session. When evaluating CHECK constraints, default values are set by the NLS parameters that were specified at database creation.

Specifying Parameters

The syntax that specifies NLS parameters in SQL functions is:

'parameter = value'

The following NLS parameters may be specified:

Only certain NLS parameters are valid for particular SQL functions, as follows:

Examples of the use of NLS parameters are

TO_DATE ('1-JAN-89', 'DD-MON-YY',
   'nls_date_language = American')

TO_CHAR (hiredate, 'DD/MON/YYYY',
   'nls_date_language = French')


TO_NUMBER ('13.000,00', '99G999D99',
   'nls_numeric_characters = ''.,''')

TO_CHAR (sal, '9G999D99L', 'nls_numeric_characters = ''.,''
   nls_currency = ''Dfl ''')

TO_CHAR (sal, '9G999D99C', 'nls_numeric_characters = '',.''
   nls_iso_currency = Japan')
NLS_UPPER (ename, 'nls_sort = Austrian')

NLSSORT (ename, 'nls_sort = German')

Note: For some languages, various lowercase characters correspond to a sequence of uppercase characters, or vice versa. As a result, the output from NLS_UPPER, NLS_LOWER, and NLS_INITCAP can differ from the length of the input.

Unacceptable Parameters

Note that NLS_LANGUAGE and NLS_TERRITORY are not accepted as parameters. Only NLS parameters that explicitly define the specific data items required for unambiguous interpretation of a format are accepted. NLS_DATE_FORMAT is also not accepted as a parameter for the reason described below.

If an NLS parameter is specified in TO_CHAR, TO_NUMBER, or TO_DATE, a format mask must also be specified as the second parameter. For example, the following specification is legal:

TO_CHAR (hiredate, 'DD/MON/YY', 'nls_date_language = French')

These are illegal:

TO_CHAR (hiredate, 'nls_date_language = French')
TO_CHAR (hiredate, 'nls_date_language = French',
   'DD/MON/YY')

This restriction means that a date format must always be specified if an NLS parameter is in a TO_CHAR or TO_DATE function. As a result, NLS_DATE_FORMAT is not a valid NLS parameter for these functions.

CONVERT Function

The SQL function CONVERT allows for conversion of character data between character sets.

For more information on CONVERT, see Oracle7 Server SQL Reference.

The CONVERT function converts the binary representation of a character string in one character set to another. It uses exactly the same technique as described previously for the conversion between database and client character sets. Hence, it uses replacement characters and has the same limitations. The syntax for CONVERT is:

where src_char_set is the source and dest_char_set is the destination character set.

NLSSORT Function

The NLSSORT function replaces a character string with the equivalent sort string used by the linguistic sort mechanism. For a binary sort, the sort string is the same as the input string. The linguistic sort technique operates by replacing each character string with some other binary values, chosen so that sorting the resulting string produces the desired sorting sequence. When a linguistic sort is being used, NLSSORT returns the binary values that replace the original string.

String Comparisons in a WHERE Clause

NLSSORT allows applications to perform string matching that follows alphabetic conventions. Normally, character strings in a WHERE clause are compared using the characters' binary values. A character is "greater than" another if it has a higher binary value in the database character set. Because the sequence of characters based on their binary values may not match the alphabetic sequence for a language, such comparisons often do not follow alphabetic conventions. For example, if a column (COL1) contains the values ABC, ABZ, BCD and ÄBC in the ISO 8859/1 8-bit character set, the following query:

SELECT COL1 FROM TAB1 WHERE COL1 > 'B'

returns both BCD and ÄBC because Ä has a higher numeric value than B. However, in German, an Ä is sorted alphabetically before B. Such conventions are language dependent even when the same character is used. In Swedish, an Ä is sorted after Z. Linguistic comparisons can be made using NLSSORT in the WHERE clause, as follows:

WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string)

Note that NLSSORT has to be on both sides of the comparison operator. For example:

SELECT COL1 FROM TAB1 WHERE NLSSORT(COL1) > NLSSORT('B')

If a German linguistic sort is being used, this does not return strings beginning with Ä because in the German alphabet Ä comes before B. If a Swedish linguistic sort is being used, such names are returned because in the Swedish alphabet Ä comes after Z.

Controlling an ORDER BY Clause

If a linguistic sorting sequence is in use, then NLSSORT is used implicitly on each character item in the ORDER BY clause. As a result, the sort mechanism (linguistic or binary) for an ORDER BY is transparent to the application. However, if the NLSSORT function is explicitly specified for a character item in an ORDER BY item, then the implicit NLSSORT is not done.

In other words, the NLSSORT linguistic replacement is only applied once, not twice. The NLSSORT function is generally not needed in an ORDER BY clause when the default sort mechanism is a linguistic sort. However, when the default sort mechanism is BINARY, then a query such as:

SELECT ENAME FROM EMP
ORDER BY ENAME

will use a binary sort. A German linguistic sort can be obtained using:

SELECT ENAME FROM EMP
ORDER BY NLSSORT(ENAME, 'NLS_SORT = GERMAN')

Contents Index Home Previous Next