Language Support   «Prev 

NLS_SORT Conversion

Setting NLS Parameters

NLS (National Language Support) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:
  1. As initialization parameters on the server
    You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:
    NLS_TERRITORY = "CZECH REPUBLIC"
    
  2. As environment variables on the client
    You can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:
    % setenv NLS_SORT FRENCH
    
  3. With the ALTER SESSION statement
    You can use NLS parameters that are set in an ALTER SESSION statement to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
    ALTER SESSION SET NLS_SORT = FRENCH;
    

1) With a normal WHERE clause, the statement returns values based on the binary order of the data
SELECT * FROM CUSTOMER WHERE NAME > 'B'
With a normal WHERE clause, the statement returns values based on the binary order of the data

2) For this German data, the statement would select the indicated rows.
SELECT * FROM CUSTOMER WHERE NAME > 'B'
For this German data, the statement would select the indicated rows.

3) With the NLS_SORT parameter, Oracle knows to select the data based on the German sort order.
SELECT * FROM CUSTOMER WHERE 
NLSSORT(NAME) > NLSSORT('B')
With the NLS_SORT parameter, Oracle knows to select the data based on the German sort order.

4) which returns the following data.
SELECT * FROM CUSTOMERS WHERE 
NLSSORT(NAME) > NLSSORT('B')

which returns the following data