Making sort in Oracle case insensitive?
This is because oracle sort is case insensitive. Most of the solutions I've seen mention setting below session parameters:
ALTER SESSION SET NLS_COMP = LINGUISTIC;
ALTER SESSION SET NLS_SORT = BINARY_CI;
By default, NLS_COMP is BINARY.
I found that if I just set NLS_SORT to BINARY_CI without setting NLS_COMP to LINGUISTIC, it still works, i.e. oracle sort will become case insensitive. Is there an advantage in setting the NLS_COMP parameter?
source to share
NLS_COMP
and NLS_SORT
have slightly different effects. NLS_COMP
, as the name suggests, for comparison. NLS_SORT
, as the name suggests, for sorting. Setting NLS_COMP
to LINGUISTIC
causes comparisons to follow the sorting rules, so the parameter is used for comparison NLS_SORT
. You can see the difference when you try:
SELECT 1 FROM DUAL WHERE 'A' = 'a';
From NLS_COMP = BINARY
the comparison is false. C NLS_COMP = LINGUISTIC
and NLS_SORT = BINARY_CI
comparison gives true.
Whether or not you should establish that depends on what results you want from your queries.
source to share