Database Reference
In-Depth Information
C1
----------
Leon
Léon
LEON
LÉON
Per default, binary comparisons are performed. To use linguistic comparisons, it's necessary to set the
nls_comp
initialization parameter to
linguistic
, and the linguistic sort sequence (and therefore the rules) used for the
comparisons must be specified through the
nls_sort
initialization parameter. The following example uses
generic_m
,
an ISO standard for Latin-based characters:
SQL> ALTER SESSION SET nls_comp = linguistic;
SQL> ALTER SESSION SET nls_sort =
generic_m
;
SQL> SELECT c1 FROM t WHERE c1 = 'LEON';
C1
----------
LEON
As expected, nothing particular happens with the previous setting. The useful feature is provided by two
extensions of
generic_m
. The first is
generic_m_ci
. With it, as shown in the following query, the comparisons are
case insensitive:
SQL> ALTER SESSION SET nls_sort =
generic_m_ci
;
SQL> SELECT c1 FROM t WHERE c1 = 'LEON';
C1
----------
Leon
LEON
The second is
generic_m_ai
. With it, as shown in the following query, the comparisons are case and accent insensitive:
SQL> ALTER SESSION SET nls_sort =
generic_m_ai
;
SQL> SELECT c1 FROM t WHERE c1 = 'LEON';
C1
----------
Leon
Léon
LEON
LÉON
From a functional point of view, this is excellent. By setting two initialization parameters, you're able to control
the behavior of the SQL operators. Let's check whether the execution plan changes when the
nls_comp
initialization
parameter is set to
linguistic
:
